
***********************************************************************************************************************;
**Code name: Part1a_Lindadata.sas
**Author: Thomas Jansson (Sveriges Riksbank)
**Latest update: June 20, 2023
**Goal: This code uses input data from the original LINDA datasets and saves the relevant variables in the GRUND folder
***********************************************************************************************************************;

/******************************/
/******* LINDA data  **********/
/******************************/

/*********************/
/** CHARACTERISTICS **/
/*********************/

*link bidnr and bidnrh (individual and household id numbers) and sample status;
%macro m0bbbab(f_or_i);
%do I = 1985 %to 2007;

	%if &f_or_i=i %then %do;
		%if &I<1999 %then %do;
			data grund.lindaFAMILY&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr nybidnrh BURVKODU);
				rename BURVKODU = BURVKODI;
			run;
		%end;

		%if &I>1998 %then %do;
			data grund.lindaFAMILY&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr nybidnrf BURVKODI);
				rename nybidnrf = nybidnrh;
			run;
		%end;
	%end;

	%if &f_or_i=f %then %do;
		%if &I<1991 %then %do;
			data grund.lindaFAMILY&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr nybidnrh BURVKODP);
				rename BURVKODP = BURVKODF;
			run;
		%end;

		%if &I>1990 %then %do;
			data grund.lindaFAMILY&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr nybidnrf BURVKODF);
				rename nybidnrf = nybidnrh;
			run;
		%end;
	%end;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbab(f);
%m0bbbab(i);


*get information about age;
%macro m0bbbaaa(f_or_i);
%do I = 1985 %to 2007;

	%if &I<1993 %then %do;
		data grund.age&f_or_i&I;			
   			set original.linda&f_or_i&I (keep = nybidnr alder);
   			age = alder*1;
			drop alder;
 		run;
	%end;

	%else %do;
		data grund.age&f_or_i&I;			
  		 	set original.linda&f_or_i&I (keep = nybidnr bald);
   			rename bald = age;
 		run;
%end;

proc sort data = grund.age&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbaaa(f);
%m0bbbaaa(i);


*get information about birth country (using the new country groups);
%macro m0bbbaaa(f_or_i);
%do I = 1985 %to 2007;

	%if &I<1993 %then %do;
		data grund.birthcountry&f_or_i&I;			
   			set original.linda&f_or_i&I (keep = nybidnr FORTCGRUPP);
   			rename FORTCGRUPP = birthcountry_gr;
 		run;
	%end;

	%if &I<1998 and &I>1992 %then %do;
		data grund.birthcountry&f_or_i&I;			
  		 	set original.linda&f_or_i&I (keep = nybidnr BFODORTGRUPP);
   			rename BFODORTGRUPP = birthcountry_gr;
 		run;
	%end;
	
	%if &I=1998 %then %do;
		data grund.birthcountry&f_or_i&I;			
   			set original.linda&f_or_i&I (keep = nybidnr BflandGRUPP);
   			rename BflandGRUPP = birthcountry_gr;
 		run;
	%end;

		%if &I>1998 %then %do;   
		data grund.birthcountry&f_or_i&I;			
   			set main.linda&f_or_i&I (keep = id birthcountry_code); *Note: Uses data from the main folder;
   			rename id = nybidnr;
		run;
	%end;

proc sort data = grund.birthcountry&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbaaa(f);
%m0bbbaaa(i);

*NOTE: In this step, birthcountry_code is changed to the country groups that were used 1985-1998. We use the individuals in LINDA 1998 who also appears in LINDA 1999-2007 
to find a link between country of birth and the country groups;

%macro m0bbbaacc;
%do I = 1999 %to 2007;

		data birthcountryf&I;
			merge grund.birthcountryf&I(in = in_left) grund.birthcountryf1998(in = in_right);
				by nybidnr;
			if in_left = 1 and in_right = 1 and birthcountry_code>'0' and birthcountry_gr>0;
			keep birthcountry_code birthcountry_gr nybidnr;
		run;

		data birthcountryi&I;
			merge grund.birthcountryi&I(in = in_left) grund.birthcountryi1998(in = in_right);
				by nybidnr;
			if in_left = 1 and in_right = 1 and birthcountry_code>'0' and birthcountry_gr>0;
			keep birthcountry_code birthcountry_gr nybidnr;
		run;

		data birthcountry&I;
			merge birthcountryf&I birthcountryi&I;
				by nybidnr;
			drop nybidnr;
		run;

		proc freq data = birthcountry&I;
			tables birthcountry_code*birthcountry_gr / noprint out = newbirthcountry&I;
		run;

		proc sort data = newbirthcountry&I;
			by birthcountry_code descending COUNT;
		run;

		proc sort nodupkey data = newbirthcountry&I dupout = multiplecountries&I;
			by birthcountry_code;
		run;

%end;
%mend;
%m0bbbaacc;


data newbirthcountry;
	set newbirthcountry1999-newbirthcountry2007;
run;

proc sort nodupkey data = newbirthcountry;
	by birthcountry_code birthcountry_gr;
run;

proc sort nodupkey data = newbirthcountry; *there should be no duplicates here;
	by birthcountry_code;
run;

%macro m0bbbaab(f_or_i);
%do I = 1985 %to 2007;

		%if &I>1998 %then %do; *Merge the link between country of birth and country groups;
		
		proc sort data = grund.birthcountry&f_or_i&I;
			by birthcountry_code;
		run;

		data grund.birthcountry&f_or_i&I;
			merge grund.birthcountry&f_or_i&I(in = in_left) newbirthcountry (keep = birthcountry_code birthcountry_gr);
  				by birthcountry_code;
			if in_left = 1;
		run;
		%end;

*get information about birth country of family members;
proc sort data = grund.birthcountry&f_or_i&I;
	by nybidnr;
run;

data grund.birthcountryFAM&f_or_i&I;
	merge grund.birthcountry&f_or_i&I grund.lindaFAMILY&f_or_i&I grund.age&f_or_i&I;
		by nybidnr;
run;

data grund.birthcountryFAMSW&f_or_i&I;
	set grund.birthcountryFAM&f_or_i&I;
		where birthcountry_gr = 15 and age>17; *born in Sweden and an adult;
	rename birthcountry_gr = swede_fam;
	keep birthcountry_gr nybidnrh;
run;

proc sort nodupkey data = grund.birthcountryFAMSW&f_or_i&I;
	by nybidnrh;
run;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnrh;
run;

data grund.lindaFAMILYSW&f_or_i&I;
	merge grund.lindaFAMILY&f_or_i&I grund.birthcountryFAMSW&f_or_i&I;
		by nybidnrh;
	rename nybidnr = id;
run;

proc sort data = grund.lindaFAMILYSW&f_or_i&I;
	by id;
run;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbaab(f);
%m0bbbaab(i);


data grund.birthcountryf;
	set grund.birthcountryf1985-grund.birthcountryf1998;
	rename nybidnr = id;
run;

proc sort nodupkey data = grund.birthcountryf;
	by id birthcountry_gr;
run;

proc sort nodupkey data = grund.birthcountryf dupout = dupsbirthcountryf;
	by id;
run;

data grund.birthcountryi;
	set grund.birthcountryi1985-grund.birthcountryi1998;
	rename nybidnr = id;
run;

proc sort nodupkey data = grund.birthcountryi;
	by id birthcountry_gr;
run;

proc sort nodupkey data = grund.birthcountryi dupout = dupsbirthcountryi;
	by id;
run;


*get information about immigration year;
%macro m000b(f_or_i);
%do I = 1985 %to 2007;

%if &I<1993 %then %do;
data grund.immigration&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr invar);
	rename invar = binvar;
run;
%end;

%if &I>1998 %then %do;
data grund.immigration&f_or_i&I;
	set main.linda&f_or_i&I (keep = id immigr_year); *Note: Use data from the main folder;
	rename immigr_year = binvar id = nybidnr;
run;
%end;

%if &f_or_i=i %then %do;
	
	%if &I>=1993 and &I<1999 %then %do;
		data grund.immigration&f_or_i&I;
			set original.linda&f_or_i&I (keep = nybidnr binvar);
				if binvar = 'OKOK' or binvar = 'OK' then binvar = .;
		run;
	%end;
%end;
	
%if &f_or_i=f %then %do;	

	%if (&I>1992 and &I<1996) or &I=1998 %then %do;
		data grund.immigration&f_or_i&I;
			set original.linda&f_or_i&I (keep = nybidnr binvar);
				if binvar = 'OKOK' or binvar = 'OK' then binvar = .;
		run;
	%end;
	
	%if (&I>1995 and &I<1998) %then %do;
		data grund.immigration&f_or_i&I;
			set original.linda&f_or_i&I (keep = nybidnr binvar);
		run;
	%end;
%end;


data grund.immigration&f_or_i&I;
	set grund.immigration&f_or_i&I;
		immigr_year&I = binvar*1;	
			drop binvar;
		if 0<immigr_year&I<100 then immigr_year&I = immigr_year&I+1900;
run;

proc sort data = grund.immigration&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m000b(f);
%m000b(i);

data grund.immigrationf;
  merge grund.immigrationf1985-grund.immigrationf2007;
	by nybidnr;
run;

data grund.immigrationi;
  merge grund.immigrationi1985-grund.immigrationi2007;
	by nybidnr;
run;


***********************************;
*get information about education   ;
***********************************;

data edukey;
	set original.WEB_SUN2000nyckelny;
		where C1 NE 'SUN  ';
		bsun = C1;
		rename C1 = utbildn C4 = bsuninr C3 = BSUNNIV;
		drop C2 C5 C6;
run;

proc sort data = edukey;
	by utbildn;
run;


%macro m0bbbabf(f_or_i);
%do I = 1985 %to 2007;

%if &I<=1990 %then %do;
	data grund.education&f_or_i&I;
		set original.linda&f_or_i&I (keep = nybidnr);
		quantedu = .; 
		quantedunew = .;
		businessedu = .;
		businesseduuni = .;
		eduuni = .;
		edu_year = .;
run;
%end;

%if &I>1989 and &I<=1992 %then %do;
	data grund.education&f_or_i&I;
		set original.linda&f_or_i&I (keep = nybidnr utbildn utbar);
		if substr(utbildn,1,1) = '3' or substr(utbildn, 1,1) = '4' then quantedu = 1; else quantedu = 0;
	run;

	proc sort data = grund.education&f_or_i&I;
		by utbildn;
	run;

	data grund.education&f_or_i&I;
		merge edukey grund.education&f_or_i&I(in = in_edu);
			by utbildn;
	if in_edu = 1;
		if substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4' then quantedunew = 1; else quantedunew = 0;
		if substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314' then businessedu = 1; else businessedu = 0;
		if (substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314') and substr(bsunniv,1,1) in ('4','5','6') then businesseduuni = 1; else businesseduuni = 0;
		if (substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4') and substr(bsunniv,1,1) in ('4','5','6') then quanteduuni = 1; else quanteduuni = 0;
		if substr(bsunniv,1,1) in ('4','5','6') then eduuni = 1; else eduuni = 0;

		if substr(bsunniv,1,2) = '00' then years_edu = 1;
		if substr(bsunniv,1,2) = '10' then years_edu = 6;
		if substr(bsunniv,1,2) = '20' then years_edu = 9;
		if substr(bsunniv,1,2) = '31' then years_edu = 10;
		if substr(bsunniv,1,2) = '32' then years_edu = 11;
		if substr(bsunniv,1,2) = '33' then years_edu = 12;
		if substr(bsunniv,1,2) = '41' then years_edu = 13;
		if substr(bsunniv,1,2) = '52' then years_edu = 14;
		if substr(bsunniv,1,2) = '53' then years_edu = 15;
		if substr(bsunniv,1,2) = '54' then years_edu = 16;
		if substr(bsunniv,1,2) = '55' then years_edu = 17;
		if substr(bsunniv,1,2) = '60' then years_edu = 17;
		if substr(bsunniv,1,2) = '62' then years_edu = 18;
		if substr(bsunniv,1,2) = '64' then years_edu = 20;

		if businessedu = 1 then years_bus_edu = years_edu*businessedu;

		edu_year = utbar*1;
		if 0<edu_year<100 then edu_year = 1900 + edu_year;

		drop bsun utbar;
	run;
%end;

		%if &I>1992 and &I<2000 %then %do;
			data grund.education&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr bsun bsunar);
				if substr(bsun,1,1) = '3' or substr(bsun,1,1) = '4' then quantedu = 1; else quantedu = 0;
			run;
			
			proc sort data = grund.education&f_or_i&I;
				by bsun;
			run;

			data grund.education&f_or_i&I;
				merge edukey grund.education&f_or_i&I(in = in_edu);
			by bsun;
			if in_edu = 1;
			if substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4' then quantedunew = 1; else quantedunew = 0;
			if substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314' then businessedu = 1; else businessedu = 0;
			if (substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314') and substr(bsunniv,1,1) in ('4','5','6') then businesseduuni = 1; else businesseduuni = 0;
			if (substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4') and substr(bsunniv,1,1) in ('4','5','6') then quanteduuni = 1; else quanteduuni = 0;
			if substr(bsunniv,1,1) in ('4','5','6') then eduuni = 1; else eduuni = 0;

		if substr(bsunniv,1,2) = '00' then years_edu = 1;
		if substr(bsunniv,1,2) = '10' then years_edu = 6;
		if substr(bsunniv,1,2) = '20' then years_edu = 9;
		if substr(bsunniv,1,2) = '31' then years_edu = 10;
		if substr(bsunniv,1,2) = '32' then years_edu = 11;
		if substr(bsunniv,1,2) = '33' then years_edu = 12;
		if substr(bsunniv,1,2) = '41' then years_edu = 13;
		if substr(bsunniv,1,2) = '52' then years_edu = 14;
		if substr(bsunniv,1,2) = '53' then years_edu = 15;
		if substr(bsunniv,1,2) = '54' then years_edu = 16;
		if substr(bsunniv,1,2) = '55' then years_edu = 17;
		if substr(bsunniv,1,2) = '60' then years_edu = 17;
		if substr(bsunniv,1,2) = '62' then years_edu = 18;
		if substr(bsunniv,1,2) = '64' then years_edu = 20;

		if businessedu = 1 then years_bus_edu = years_edu*businessedu;

		edu_year = bsunar*1;
		if 0<edu_year<100 then edu_year = 1900 + edu_year;
		
			drop utbildn bsunar;
			run;

		%end;

		%if &I>=2000 %then %do;
			data grund.education&f_or_i&I;
				set original.linda&f_or_i&I (keep = nybidnr bsuninr BSUNNIV bsunar);
				if substr(bsuninr, 1,1) = '3' or substr(bsuninr, 1,1) = '4' then quantedu = 1; else quantedu = 0;
				if substr(bsuninr, 1,2) = '34' or substr(bsuninr, 1,3) = '314' then businessedu = 1; else businessedu = 0;
				if (substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314') and substr(bsunniv,1,1) in ('4','5','6') then businesseduuni = 1; else businesseduuni = 0;
				if (substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4') and substr(bsunniv,1,1) in ('4','5','6') then quanteduuni = 1; else quanteduuni = 0;
				if substr(bsunniv,1,1) in ('4','5','6') then eduuni = 1; else eduuni = 0;
				quantedunew = quantedu;

		if substr(bsunniv,1,2) = '00' then years_edu = 1;
		if substr(bsunniv,1,2) = '10' then years_edu = 6;
		if substr(bsunniv,1,2) = '20' then years_edu = 9;
		if substr(bsunniv,1,2) = '31' then years_edu = 10;
		if substr(bsunniv,1,2) = '32' then years_edu = 11;
		if substr(bsunniv,1,2) = '33' then years_edu = 12;
		if substr(bsunniv,1,2) = '41' then years_edu = 13;
		if substr(bsunniv,1,2) = '52' then years_edu = 14;
		if substr(bsunniv,1,2) = '53' then years_edu = 15;
		if substr(bsunniv,1,2) = '54' then years_edu = 16;
		if substr(bsunniv,1,2) = '55' then years_edu = 17;
		if substr(bsunniv,1,2) = '60' then years_edu = 17;
		if substr(bsunniv,1,2) = '62' then years_edu = 18;
		if substr(bsunniv,1,2) = '64' then years_edu = 20;

		if businessedu = 1 then years_bus_edu = years_edu*businessedu;

		edu_year = bsunar*1;
		
		drop bsunar;

			run;
		%end;

%if &f_or_i = f %then %do;
	%if &I = 1996 %then %do;
			data grund.education&f_or_i&I;
			length bsunnew $5;
				set original.linda&f_or_i&I (keep = nybidnr bsun bsunar);
				bsunUP = bsun+100000;
				bsunadj = put(bsunUP,6.);
				bsunnew = substr(bsunadj,2,5);
				if substr(bsunnew, 1,1) = '3' or substr(bsunnew, 1,1) = '4' then quantedu = 1; else quantedu = 0;
				drop bsun bsunUP bsunadj;
				rename bsunnew = bsun;
			run;

			proc sort data = grund.education&f_or_i&I;
				by bsun;
			run;

			data grund.education&f_or_i&I;
				merge edukey grund.education&f_or_i&I(in = in_edu);
			by bsun;
			if in_edu = 1;
			if substr(bsuninr, 1,1) = '3' or substr(bsuninr, 1,1) = '4' then quantedunew = 1; else quantedunew = 0;
			if substr(bsuninr, 1,2) = '34' or substr(bsuninr, 1,3) = '314' then businessedu = 1; else businessedu = 0;
			if (substr(bsuninr,1,2) = '34' or substr(bsuninr,1,3) = '314') and substr(bsunniv,1,1) in ('4','5','6') then businesseduuni = 1; else businesseduuni = 0;
			if (substr(bsuninr,1,1) = '3' or substr(bsuninr,1,1) = '4') and substr(bsunniv,1,1) in ('4','5','6') then quanteduuni = 1; else quanteduuni = 0;
			if substr(bsunniv,1,1) in ('4','5','6') then eduuni = 1; else eduuni = 0;

		if substr(bsunniv,1,2) = '00' then years_edu = 1;
		if substr(bsunniv,1,2) = '10' then years_edu = 6;
		if substr(bsunniv,1,2) = '20' then years_edu = 9;
		if substr(bsunniv,1,2) = '31' then years_edu = 10;
		if substr(bsunniv,1,2) = '32' then years_edu = 11;
		if substr(bsunniv,1,2) = '33' then years_edu = 12;
		if substr(bsunniv,1,2) = '41' then years_edu = 13;
		if substr(bsunniv,1,2) = '52' then years_edu = 14;
		if substr(bsunniv,1,2) = '53' then years_edu = 15;
		if substr(bsunniv,1,2) = '54' then years_edu = 16;
		if substr(bsunniv,1,2) = '55' then years_edu = 17;
		if substr(bsunniv,1,2) = '60' then years_edu = 17;
		if substr(bsunniv,1,2) = '62' then years_edu = 18;
		if substr(bsunniv,1,2) = '64' then years_edu = 20;

		if businessedu = 1 then years_bus_edu = years_edu*businessedu;

		edu_year = bsunar*1;
		if 0<edu_year<100 then edu_year = 1900 + edu_year;

			drop utbildn bsunar;
			run;
	%end;
%end;

proc sort data = grund.education&f_or_i&I;
	by nybidnr;
run;

%if &I>1989 %then %do;
data grund.edu_arrival&f_or_i&I;
	set grund.education&f_or_i&I;
		keep nybidnr bsuninr bsunniv edu_year years_edu;
	rename bsuninr = bsuninr&I bsunniv = bsunniv&I edu_year = edu_year&I years_edu = years_edu&I nybidnr = id;
run;
%end;


%end;
%mend;
%m0bbbabf(f);
%m0bbbabf(i);


data grund.edu_arrivalf;
	merge grund.edu_arrivalf1991-grund.edu_arrivalf2007;
		by id;
run;

data grund.edu_arrivali;
	merge grund.edu_arrivali1991-grund.edu_arrivali2007;
		by id;
run;

proc export data = grund.edu_arrivalf
		 outfile = "\\micro.intra\projekt\P0459$\P0459_gem\SAS DATA\Thomas\Projekt2\RFS\bal\edu_arrivalf.csv" dbms=csv replace;
run;


data grund.educationf;
	set grund.educationf1999-grund.educationf2000;
run;

proc sort data = grund.educationf;
	by nybidnr;
run;

data grund.educationi;
	set grund.educationi1999-grund.educationi2000;
run;

proc sort data = grund.educationi;
	by nybidnr;
run;


********************************************;
*get information about location of resicence;
********************************************;

*This first step is to link property ids and electoral districts between 1985 and 1991*;

%macro m00b(f_or_i);
%do I = 1985 %to 1991;

%if &I<1991 %then %do;
data bostadsort&f_or_i&I;
	length parish&I $6;
	set original.linda&f_or_i&I (keep = nybidnr IDMANT KBH DIST);
		parish&I = substr(KBH,1,6);
		if IDMANT>'0' and DIST>'0' then election&I = IDMANT || DIST;
		rename IDMANT = parishNOV&I KBH = property&I DIST = DIST&I;
run;
%end;

%if &I = 1991 %then %do;
data bostadsort&f_or_i&I;
	length parish&I $6;
	set original.linda&f_or_i&I (keep = nybidnr IDMANT AKBF DIST);
		parish&I = substr(AKBF,1,6);
		if IDMANT>'0' and DIST>'0' then election&I = IDMANT || DIST;
		rename IDMANT = parishNOV&I AKBF = property&I DIST = DIST&I;
run;
%end;

proc sort data = bostadsort&f_or_i&I;
	by nybidnr;
run;

%end;

data bostad_all&f_or_i;
	merge bostadsort&f_or_i.1985-bostadsort&f_or_i.1991;
		by nybidnr;
run;

%mend;
%m00b(f);
%m00b(i);


%macro m00bv(f_or_i);
%do j = 1985 %to 1989;
%let leadyear = %eval(&j+1);
%let leadyear2 = %eval(&j+2);

data mergedbostadsort&f_or_i&j;
	merge bostadsort&f_or_i&j bostadsort&f_or_i&leadyear bostadsort&f_or_i&leadyear2 bostadsorti&j bostadsorti&leadyear bostadsorti&leadyear2;
		by nybidnr;	
run;

data merged2bostadsort&f_or_i&j;
	set mergedbostadsort&f_or_i&j;
		where property&j = property&leadyear = property&leadyear2 and property&j>'00' 
		and parish&j = parish&leadyear = parish&leadyear2 = parishNOV&leadyear = parishNOV&leadyear2 and parish&j>'00'
		and election&leadyear = election&leadyear2 and election&leadyear>'00';
run;

proc sort data = merged2bostadsort&f_or_i&j nodupkey dupout = dups2bostadsort&f_or_i&j out = merged3bostadsort&f_or_i&j;
	by property&j election&leadyear;
run;

proc sort data = merged3bostadsort&f_or_i&j nodupkey dupout = dups3bostadsort&f_or_i&j out = merged4bostadsort&f_or_i&j;
	by property&j;
run;

data merged5bostadsort&f_or_i&j;
	set merged4bostadsort&f_or_i&j;
		keep property&j election&leadyear;
		rename property&j = property election&leadyear = election_imp&j;
run;

%end;

data all_merged;
	merge merged5bostadsortf1985-merged5bostadsortf1989;
		by property;
run;

%mend;
%m00bv(f);

data all_merged2;
	set all_merged;
		max_election = max(of election_imp1987-election_imp1988);
		min_election = min(of election_imp1987-election_imp1988);	
			if min_election = max_election then correct = 1; else correct = 0;	
			if correct = 1 and election_imp1987>'0' then election_imp = election_imp1987;
			if correct = 1 and election_imp1988>'0' then election_imp = election_imp1988;
 			if correct = 1 and max_election =. and election_imp1989>'0' then election_imp = election_imp1989;
			if correct = 1 and election_imp1987 ='' and election_imp1988 ='' and election_imp1989 ='' and election_imp1986 >'0' then election_imp = election_imp1986;
			if correct = 1 and election_imp1986 ='' and election_imp1987 ='' and election_imp1988 ='' and election_imp1989 ='' and election_imp1985 >'0' then election_imp = election_imp1985;
run;

data grund.propertyelection;
	set all_merged2;
		where election_imp>'0' and property>'0';
	keep property election_imp;
run;

****************************************************************************;
%macro m00b(f_or_i);
%do I = 1985 %to 2007;
%let lagyear = %eval(&I-1);
%let leadyear = %eval(&I+1);

%if &I<1991 %then %do;
data grund.bostadsort&f_or_i&I;
	length parish&I $6 muni&I $4 county&I $2;
	set original.linda&f_or_i&I (keep = nybidnr IDMANT KBH PANR DIST);
		parish&I = substr(KBH,1,6);
		county&I = substr(KBH,1,2);
		muni&I = substr(KBH,1,4);
		if IDMANT>'0' and DIST>'0' then election&lagyear = IDMANT || DIST;
		rename IDMANT = parishNOV&I KBH = property&I PANR = zipcode&I DIST = district&I;
run;
%end;

%if &I = 1991 %then %do;
data grund.bostadsort&f_or_i&I;
	length parish&I $6 muni&I $4 county&I $2;
	set original.linda&f_or_i&I (keep = nybidnr IDMANT AKBF PANR DIST);
		parish&I = substr(AKBF,1,6);
		county&I = substr(AKBF,1,2);
		muni&I = substr(AKBF,1,4);
		if IDMANT>'0' and DIST>'0' then election&lagyear = IDMANT || DIST;
		rename IDMANT = parishNOV&I AKBF = property&I PANR = zipcode&I DIST = district&I;
run;
%end;

%if &I = 1992 %then %do;
data grund.bostadsort&f_or_i&I;
	length parish&I $6 muni&I $4 county&I $2;
	set original.linda&f_or_i&I (keep = nybidnr IDMANT AKBF PANR);
		parish&I = substr(AKBF,1,6);
		county&I = substr(AKBF,1,2);
		muni&I = substr(AKBF,1,4);
		rename IDMANT = parishNOV&I AKBF = property&I PANR = zipcode&I;	
run;
%end;

%if &I>=1993 and &I<1997 %then %do;
data grund.bostadsort&f_or_i&I;
	length parishNOV&I $6 parish&I $6 property&I $11 muni&I $4 county&I $2;
set original.linda&f_or_i&I (keep = nybidnr blkfnov BLKFF BPOSTNR);
			blkfnovUP = blkfnov+1000000;
			regionup = put(blkfnovUP,7.);
			parishNOV&I = substr(regionup,2,6);
				BLKFFUP = BLKFF+100000000000;
				regionup2 = put(BLKFFUP,12.);
				property&I = substr(regionup2,2,11);
				parish&I = substr(regionup2,2,6);
				county&I = substr(regionup2,2,2);
				muni&I = substr(regionup2,2,4);
				zipcode&I = put(BPOSTNR,5.);
	drop blkfnovUP blkfnov regionup BLKFFUP BLKFF regionup2 BPOSTNR;
run;
%end;

%if &I = 1997 %then %do;
data grund.bostadsort&f_or_i&I;
	length parishNOV&I $6 parish&I $6 muni&I $4 county&I $2 propertyid $5;
set original.linda&f_or_i&I (keep = nybidnr blkfnov BLKF BFAST);
			blkfnovUP = blkfnov+1000000;
			regionup = put(blkfnovUP,7.);
			parishNOV&I = substr(regionup,2,6);
				BLKFFUP = BLKF+1000000;
				regionup2 = put(BLKFFUP,7.);
				parish&I = substr(regionup2,2,6);
					BFASTUP = BFAST+100000;
					BFASTUP2 = put(BFASTUP,6.);
					propertyid = substr(BFASTUP2,2,5);
				if parish&I>'0' and propertyid>'0' then property&I = parish&I || propertyid;
				county&I = substr(regionup2,2,2);
				muni&I = substr(regionup2,2,4);
	drop blkfnovUP blkfnov regionup BLKFFUP BLKF regionup2 BFAST BFASTUP BFASTUP2 propertyid;
run;
%end;

%if &I>1990 and &I<1998 %then %do; *use the link between properties and electoral districts;
proc sort data = grund.bostadsort&f_or_i&I;
	by property&I;
run;

data grund.bostadsort&f_or_i&I;
	merge grund.bostadsort&f_or_i&I(in = in_bostad) grund.propertyelection(rename = (property = property&I));
		by property&I;
			if in_bostad = 1;
		rename election_imp = election&I;
run;
%end;

%if &I = 1998 %then %do;
data grund.bostadsort&f_or_i&I;
	length parishNOV&I $6  parish&I $6 property&I $11 muni&I $4 county&I $2;
set original.linda&f_or_i&I (keep = nybidnr blkfnov BLKF);
			blkfnovUP = blkfnov+1000000;
			regionup = put(blkfnovUP,7.);
			parishNOV&I = substr(regionup,2,6);
				BLKFFUP = BLKF+1000000;
				regionup2 = put(BLKFFUP,7.);
				parish&I = substr(regionup2,2,6);
				property&I = substr(regionup2,2,6); *there is no information about property;
				county&I = substr(regionup2,2,2);
				muni&I = substr(regionup2,2,4);
	drop blkfnovUP blkfnov regionup BLKFFUP BLKF regionup2;
run;
%end;

%if &I>1998 %then %do;
data grund.bostadsort&f_or_i&I;
	length parishNOV&I $6 muniNOV&I $4 parish&I $6 property&I $11 muni&I $4 county&I $2 distup $2;
set original.linda&f_or_i&I (keep = nybidnr blkfnov BLKF BVALDIS);
			blkfnovUP = blkfnov+1000000;
			regionup = put(blkfnovUP,7.);
			parishNOV&I = substr(regionup,2,6);
			muniNOV&I = substr(regionup,2,4);
				BLKFFUP = BLKF+1000000;
				regionup2 = put(BLKFFUP,7.);
				parish&I = substr(regionup2,2,6);
				property&I = substr(regionup2,2,6);
				county&I = substr(regionup2,2,2);
				muni&I = substr(regionup2,2,4);
				BVALDISUP = BVALDIS+100;
				dist = put(BVALDISUP,3.);
				distup = substr(dist,2,2);
				if parishNOV&I>'0' and distup>'0' then election&lagyear = parishNOV&I || distup;
				*if muniNOV&I>'0' and distup>'0' then electionmuni&lagyear = muniNOV&I || distup;
	drop blkfnovUP blkfnov regionup BLKFFUP BLKF regionup2 dist;
	rename distup = district&I;	
run;
%end;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

%end;

data grund.bostadsort2&f_or_i;
	merge grund.bostadsort&f_or_i.1985-grund.bostadsort&f_or_i.2007;
		by nybidnr;
		rename nybidnr = id;
run;

%do j = 1985 %to 1990;
%let leadyear = %eval(&j+1);

data grund.bostadsort&f_or_i&j;
	merge grund.bostadsort&f_or_i&j(in = in_grund) grund.bostadsort&f_or_i&leadyear(keep = nybidnr election&j parishNOV&leadyear rename = (parishNOV&leadyear = parishNOVthis&j));
		by nybidnr;
		if in_grund = 1;
run;
%end;

%do k = 1991 %to 1997;
%let leadyear = %eval(&k+1);

data grund.bostadsort&f_or_i&k;
	merge grund.bostadsort&f_or_i&k(in = in_grund) grund.bostadsort&f_or_i&leadyear(keep = nybidnr parishNOV&leadyear rename = (parishNOV&leadyear = parishNOVthis&k));
		by nybidnr;
		if in_grund = 1;
		if parishNOVthis&k NE parish&k then election&k = '';
run;
%end;

%do l = 1998 %to 2006;
%let leadyear = %eval(&l+1);

data grund.bostadsort&f_or_i&l;
	merge grund.bostadsort&f_or_i&l(in = in_grund) grund.bostadsort&f_or_i&leadyear(keep = nybidnr election&l parishNOV&leadyear rename = (parishNOV&leadyear = parishNOVthis&l));
		by nybidnr;
		if in_grund = 1;
run;
%end;

%do m = 2007 %to 2007;
data grund.bostadsort&f_or_i&m;
	set grund.bostadsort&f_or_i&m;
	election&m ='';
	parishNOVthis&m='';
run;
%end;

%mend;
%m00b(f);
%m00b(i);

data grund.bostadsortf;
	merge grund.bostadsortf1985-grund.bostadsortf2007;
		by nybidnr;
		rename nybidnr = id;
run;

data grund.bostadsorti;
	merge grund.bostadsorti1985-grund.bostadsorti2007;
		by nybidnr;
		rename nybidnr = id;
run;


*Use the property information in the data. Summarize number of individuals per property;
%macro m0bbbaa;
%do I = 1985 %to 2007;
%let lagyear = %eval(&I-3);

%if &I<1996 %then %do;
data properties&I;
	merge grund.bostadsortf&I grund.bostadsorti&I grund.immigrationf&I grund.immigrationi&I grund.birthcountryf&I grund.birthcountryi&I;
		by nybidnr;
run;

proc sort nodupkey data = properties&I;
	by nybidnr;
run;

proc sort data = properties&I;
	by property&I;
run;

data propertycount&I;
	set properties&I;
	   by property&I;
   		if first.property&I then do;
         num_property&I = 0; 
      	end;
   		 num_property&I + 1;
	if last.property&I then output;
	keep property&I num_property&I;
run;

data propertycountSE&I;
	set properties&I;
	where birthcountry_gr = 15;
	   by property&I;
   		if first.property&I then do;
         num_propertySE&I = 0; 
      	end;
   		 num_propertySE&I + 1;
	if last.property&I then output;
	keep property&I num_propertySE&I;
run;

data propertycountIMMI&I;
	set properties&I;
	where immigr_year&I>=&lagyear;
	   by property&I;
   		if first.property&I then do;
         num_propertyIMMI&I = 0; 
      	end;
   		 num_propertyIMMI&I + 1;
	if last.property&I then output;
	keep property&I num_propertyIMMI&I;
run;
%end;

%end;
%mend;
%m0bbbaa;


/********************/
/** Household head **/
/********************/

%macro m0bbbaaadfgg(f_or_i);
%do I = 1986 %to 2007;

%if &I<1991 %then %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr din86 bkon);
		rename din86 = disp_inc;
run;
%end;

%else %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr cdispl bkon);
		rename cdispl = disp_inc;
run;
%end;

proc sort data = income&f_or_i&I;
	by nybidnr;
run; 

data householdhead&f_or_i&I;
	merge grund.lindafamily&f_or_i&I grund.age&f_or_i&I income&f_or_i&I;;
		by nybidnr;
			over18 = (age>=18);
			man = (bkon=1);
run;

proc sort data = householdhead&f_or_i&I;
	by nybidnrh over18 disp_inc age man; *sort by adult (over the age of 18), disposable income, age, and gender;
run; 

data heads&f_or_i&I;
	set householdhead&f_or_i&I;
		by nybidnrh;
			if last.nybidnrh then head = 1;
			else head = 0;
run;

proc sort data = heads&f_or_i&I;
	by nybidnr;
run;

%if &I>1990 %then %do;
data headedu&f_or_i&I;
	merge heads&f_or_i&I(keep=nybidnrh nybidnr head age) grund.education&f_or_i&I;
		by nybidnr;
run;
%end;

%if &I<1991 %then %do;
data headedu&f_or_i&I;
	merge heads&f_or_i&I(in = in_head keep=nybidnrh nybidnr head age) grund.education&f_or_i.1991; *use education info from 1991;
		by nybidnr;
			if in_head = 1;
run;
%end;

data headedumod&f_or_i&I;
	set headedu&f_or_i&I;
		where head = 1; *keep only household head;
			drop nybidnr;
run;

proc sort data =  headedumod&f_or_i&I;
	by nybidnrh;
run;

proc sort data =  heads&f_or_i&I;
	by nybidnrh;
run;

*create max education level per household;
proc sort data = headedu&f_or_i&I;
	by nybidnrh years_edu;
run;

data headedumaxtemp&f_or_i&I;
	set headedu&f_or_i&I;
		by nybidnrh;
			if last.nybidnrh then maxedu = 1;
			else maxedu = 0;
run;

data headedumax&f_or_i&I;
	set headedumaxtemp&f_or_i&I;
		where maxedu = 1;
run;

*merge;
data grund.headedu&f_or_i&I (keep = nybidnr eduuni years_edu years_edu_max eduuni_max age_max age);
	merge headedumod&f_or_i&I heads&f_or_i&I headedumax&f_or_i&I(keep = years_edu eduuni age nybidnrh rename = (years_edu = years_edu_max eduuni = eduuni_max age = age_max));
		by nybidnrh;
run;

*create separate household head dataset;
data grund.hhhead&f_or_i&I;
	set heads&f_or_i&I;
		keep nybidnr head;
run;

*sort;
proc sort data = grund.hhhead&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.headedu&f_or_i&I;
	by nybidnr;
run;
	
%end;
%mend;
%m0bbbaaadfgg(f);
%m0bbbaaadfgg(i);


/****************************/
/** Industries and sectors **/
/****************************/

*get information about industry and sector;
%macro m0bbbaaa(f_or_i);
%do I = 1987 %to 2007;

	%if &I<1993 %then %do;
		data grund.industry&f_or_i&I;			
   			set original.linda&f_or_i&I (keep = nybidnr kuinst kusni);
			if substr(kuinst,1,1) = '3' then finsector = 1; else finsector = 0;	
			sni_alt = .; 						* Industrial code (SNI);
			sni = .;
			sni_entr = .;
			sni_2 =.;
			sni_entr_2 =.;
			sni_1 =.;
			sni_entr_1 =.;
			if '81000'<=kusni<'82000' then banking = 1; else banking = 0;
			if kusni='81010' then banking_mod = 1; else banking_mod = 0;
			
		run;
	%end;

	%if &I>1992 and &I<1999 %then %do;
		data grund.industry&f_or_i&I;			
   			set original.linda&f_or_i&I (keep = nybidnr BKUINST BKUNGR);
			sector = BKUINST*1;
			sector2 = put(BKUINST,4.);
			if substr(sector2,1,1) = '3' then finsector = 1; else finsector = 0;
			sni_alt = input(BKUNGR,best5.); 	* Industrial code (SNI);
			sni = .;
			sni_entr = .;
			sni_2 = substr(BKUNGR,1,2);
			sni_entr_2 =.;
			sni_1 =.;
			sni_entr_1 =.;
			%if &I>1995 and &I<1998 and &f_or_i=f %then %do;
			sni_alt = BKUNGR; 
			BKUNGR_temp = put(BKUNGR,5.);
			sni_2 = substr(BKUNGR_temp,1,2);
			drop BKUNGR_temp;
			%end;
			%if &I>1996 and &I<1998 and &f_or_i=i %then %do;
			sni_alt = BKUNGR; 
			BKUNGR_temp = put(BKUNGR,5.);
			sni_2 = substr(BKUNGR_temp,1,2);
			drop BKUNGR_temp;
			%end;

		run;
	%end;

	%if &I>1998 %then %do;
		data grund.industry&f_or_i&I;			
  		 	set original.linda&f_or_i&I (keep = nybidnr BKUINST BKUNGR bngr bngre);
			if substr(BKUINST,1,1) = '2' then finsector = 1; else finsector = 0; * Work in financial sector (dummy);
			sni_alt = input(BKUNGR,best5.); 					* Industrial code (SNI);
			rename bngr = sni bngre = sni_entr;
			sni_2 = substr(bngr,1,2);
			sni_1 = substr(bngr,1,1);
			sni_entr_2 = substr(bngre,1,2);	
			sni_entr_1 = substr(bngre,1,1);		
		run;

		%if &I>2001 %then %do;
		data grund.snikod&f_or_i&I;			
  		 	set original.linda&f_or_i&I (keep = nybidnr BKUNGR);
			bransch_sni92 = .;
			bransch_sni02 = input(BKUNGR,best5.);
			id = nybidnr;
			keep id bransch_sni92 bransch_sni02;
		run;
		%end;

		%if &I<2002 %then %do;
		data grund.snikod&f_or_i&I;			
  		 	set original.linda&f_or_i&I (keep = nybidnr BKUNGR);
			bransch_sni02 = .;
			bransch_sni92 = input(BKUNGR,best5.);
			id = nybidnr;
			keep id bransch_sni92 bransch_sni02;
		run;
		%end;

		proc sort data = grund.snikod&f_or_i&I;
			by id;
		run;
%end;

data grund.industry&f_or_i&I;
	set grund.industry&f_or_i&I;
		label sni_alt = 'Alt. indu. code (SNI)';
	id = nybidnr;
	keep nybidnr id finsector sni_alt sni sni_1 sni_2 sni_entr sni_entr_1 sni_entr_2 %if &I<1993 %then %do; banking banking_mod %end;;
run;

proc sort data = grund.industry&f_or_i&I;
	by id;
run;

data tempindustry&f_or_i&I;
	set grund.industry&f_or_i&I;
		rename sni_entr_2 = sni_entr_2_&I sni_2 = sni_2_&I;
run;

data modtempindustry&f_or_i&I;
	set tempindustry&f_or_i&I;
		rename sni_alt = sni_alt_&I;
	keep id sni_alt sni_2_&I;
run;

%end;
%mend;
%m0bbbaaa(f);
%m0bbbaaa(i);


data grund.industry_2_1993f;
set grund.industryf1993;
	rename sni_2 = sni_2_1993;
	keep id sni_2;
run;

data grund.industry_2_1995f;
set grund.industryf1995;
	rename sni_2 = sni_2_1995;
	keep id sni_2;
run;

data grund.industry_2_1993i;
set grund.industryi1993;
	rename sni_2 = sni_2_1993;
	keep id sni_2;
run;

data grund.industry_2_1995i;
set grund.industryi1995;
	rename sni_2 = sni_2_1995;
	keep id sni_2;
run;


data grund.industryf;
	merge tempindustryf1999-tempindustryf2007;
		by id;
	keep id sni_entr_2_1999-sni_entr_2_2007 sni_2_1999-sni_2_2007;
run;

data grund.industryi;
	merge tempindustryi1999-tempindustryi2007;
		by id;
	keep id sni_entr_2_1999-sni_entr_2_2007 sni_2_1999-sni_2_2007;
run;


%macro m0bbbaaaa(f_or_i);
%do I = 1987 %to 1992;

data industry_group&f_or_i&I;			
   	set original.linda&f_or_i&I (keep = nybidnr kusni);			
run;

proc sort data = industry_group&f_or_i&I;
	by nybidnr;
run;

data industry_group2&f_or_i&I;
	merge industry_group&f_or_i&I grund.bostadsort&f_or_i&I;
		by nybidnr;
run;

data industry_group2&f_or_i&I;
	set industry_group2&f_or_i&I;
		where kusni>'00001' and election&I>'0';
			
*sectors of the economy;
agriculture = 0;
if '10000'<=kusni<'12000' then agriculture = 1;
if agriculture = 1 then sector = 1;

forestry = 0;
if '12000'<=kusni<'13000' then forestry = 1;
if forestry = 1 then sector = 2;

fishing = 0;
if '13000'<=kusni<'21000' then fishing = 1;
if fishing = 1 then sector = 3;

coalmining = 0;
if '21000'<=kusni<'22000' then coalmining = 1;
if coalmining = 1 then sector = 4;

oilmining = 0;
if '22000'<=kusni<'23000' then oilmining = 1;
if oilmining = 1 then sector = 5;

uranium = 0;

ironore = 0;
if '23000'<=kusni<'29000' then ironore = 1;
if ironore = 1 then sector = 7;

stones = 0;
if '29000'<=kusni<'31000' then stones = 1;
if stones = 1 then sector = 8;

foodman = 0;
if '31000'<=kusni<'31400' then foodman = 1;
if foodman = 1 then sector = 9;

tobacco = 0;
if '31400'<=kusni<'32000' then tobacco = 1;
if tobacco = 1 then sector = 10;

textile = 0;
if '32000'<=kusni<'32200' then textile = 1;
if textile = 1 then sector = 11;

clothing = 0;
if '32200'<=kusni<'32330' then clothing = 1;
if clothing = 1 then sector = 12;

leather = 0;
if '32330'<=kusni<'33000' then leather = 1;
if leather = 1 then sector = 13;

wood = 0;
if '33000'<=kusni<'34000' then wood = 1;
if wood = 1 then sector = 14;

paper = 0;
if '34000'<=kusni<'34200' then paper = 1;
if paper = 1 then sector = 15;

publishing = 0;
if '34200'<=kusni<'35000' then publishing = 1;
if publishing = 1 then sector = 16;

cokes = 0;
if '35300'<=kusni<'35500' then cokes = 1;
if cokes = 1 then sector = 17;

chemicals = 0;
if '35000'<=kusni<'35300' then chemicals = 1;
if chemicals = 1 then sector = 18;

rubber = 0;
if '35500'<=kusni<'36000' then rubber = 1;
if rubber = 1 then sector = 19;

minerals = 0;
if '36000'<=kusni<'37000' then minerals = 1;
if minerals = 1 then sector = 20;

metals = 0;
if '37000'<=kusni<'38000' then metals = 1;
if metals = 1 then sector = 21;

metalproducts = 0;
if '38000'<=kusni<'38200' then metalproducts = 1;
if metalproducts = 1 then sector = 22;

machinery = 0;
if '38200'<=kusni<'38300' then machinery = 1;
if machinery = 1 then sector = 23;

computers = 0;
if '38320'<=kusni<'38330' then computers = 1;
if computers = 1 then sector = 24;

generators = 0;
if '38300'<=kusni<'38320' or '38390'<=kusni<'38400' then generators = 1;
if generators = 1 then sector = 25;

televisions = 0;
if '38330'<=kusni<'38340' then televisions = 1;
if televisions = 1 then sector = 26;

medicals = 0;
if '38500'<=kusni<'39000' then medicals = 1;
if medicals = 1 then sector = 27;

cars = 0;
if '38430'<=kusni<'38440' then cars = 1;
if cars = 1 then sector = 28;

boats = 0;
if '38400'<=kusni<'38430' or '38440'<=kusni<'38500' then boats = 1;
if boats = 1 then sector = 29;

furniture = 0;
if '39000'<=kusni<'41000' then furniture = 1;
if furniture = 1 then sector = 30;

recycling = 0;

energy = 0;
if '41000'<=kusni<'42000' then energy = 1;
if energy = 1 then sector = 32;

water = 0;
if '42000'<=kusni<'50000' then water = 1;
if water = 1 then sector = 33;

construction = 0;
if '50000'<=kusni<'61000' then construction = 1;
if construction = 1 then sector = 34;

carsales = 0;
if '61300'<=kusni<'62000' then carsales = 1;
if carsales = 1 then sector = 35;

wholesale = 0;
if '61000'<=kusni<'61300' then wholesale = 1;
if wholesale = 1 then sector = 36;

retail = 0;
if '62000'<=kusni<'63000' or '95000'<=kusni<'95200' then retail = 1;
if retail = 1 then sector = 37;

hotels = 0;
if '63000'<=kusni<'71000' then hotels = 1;
if hotels = 1 then sector = 38;

landtransport = 0;
if '71000'<=kusni<'71200' then landtransport = 1;
if landtransport = 1 then sector = 39;

seatransport = 0;
if '71200'<=kusni<'71300' then seatransport = 1;
if seatransport = 1 then sector = 40;

airtransport = 0;
if '71300'<=kusni<'71900' then airtransport = 1;
if airtransport = 1 then sector = 41;

supporttransport = 0;
if '71900'<=kusni<'72000' then supporttransport = 1;
if supporttransport = 1 then sector = 42;

postal = 0;
if '72000'<=kusni<'81000' then postal = 1;
if postal = 1 then sector = 43;

banking = 0;
if '81000'<=kusni<'82000' then banking = 1;
if banking = 1 then sector = 44;

insurance = 0;
if '82000'<=kusni<'83000' then insurance = 1;
if insurance = 1 then sector = 45;

brokers = 0;

realestate = 0;
if '83000'<=kusni<'83200' then realestate = 1;
if realestate = 1 then sector = 47;

rentals = 0;
if '83300'<=kusni<'83400' then rentals = 1;
if rentals = 1 then sector = 48;

data = 0;
if '83230'<=kusni<'83240' then data = 1;
if data = 1 then sector = 49;

rd = 0;

corporate = 0;
if '83200'<=kusni<'83230' or '83240'<=kusni<'83300' then corporate = 1;
if corporate = 1 then sector = 51;

public = 0;
if '91000'<=kusni<'92000' then public = 1;
if public = 1 then sector = 52;

education = 0;
if '93000'<=kusni<'93300' then education = 1;
if education = 1 then sector = 53;

health = 0;
if '93300'<=kusni<'93500' then health = 1;
if health = 1 then sector = 54;

waste = 0;
if '92000'<=kusni<'93000' then waste = 1;
if waste = 1 then sector = 55;

organizations = 0;
if '93500'<=kusni<'94000' then organizations = 1;
if organizations = 1 then sector = 56;

entertainment = 0;
if '94000'<=kusni<'95000' then entertainment = 1;
if entertainment = 1 then sector = 57;

drycleaning = 0;
if '95200'<=kusni<'96000' then drycleaning = 1;
if drycleaning = 1 then sector = 58;

households = 0;

foreign = 0;
if '96000'<=kusni<'99999' then foreign = 1;
if foreign = 1 then sector = 60;


* Construct 1-digit industry code;
if agriculture=1 then industry=1;
if forestry=1 then industry=1;

if fishing=1 then industry=2;

if coalmining=1 then industry=3;
if oilmining=1 then industry=3;
if uranium=1 then industry=3;
if ironore=1 then industry=3;
if stones=1 then industry=3;

if foodman=1 then industry=4;
if tobacco=1 then industry=4;
if textile=1 then industry=4;
if clothing=1 then industry=4;
if leather=1 then industry=4;
if wood=1 then industry=4;
if paper=1 then industry=4;
if publishing=1 then industry=4;
if cokes=1 then industry=4;
if chemicals=1 then industry=4;
if rubber=1 then industry=4;
if minerals=1 then industry=4;
if metals=1 then industry=4;
if metalproducts=1 then industry=4;
if machinery=1 then industry=4;
if computers=1 then industry=4;
if generators=1 then industry=4;
if televisions=1 then industry=4;
if medicals=1 then industry=4;
if cars=1 then industry=4;
if boats=1 then industry=4;
if furniture=1 then industry=4;
if recycling=1 then industry=4;

if energy=1 then industry=5;
if water=1 then industry=5;

if construction=1 then industry=6;

if carsales=1 then industry=7;
if wholesale=1 then industry=7;
if retail=1 then industry=7;

if hotels=1 then industry=8;

if landtransport=1 then industry=9;
if seatransport=1 then industry=9;
if airtransport=1 then industry=9;
if supporttransport=1 then industry=9;
if postal=1 then industry=9;

if banking=1 then industry=10;
if insurance=1 then industry=10;
if brokers=1 then industry=10;

if realestate=1 then industry=11;
if rentals=1 then industry=11;
if data=1 then industry=11;
if rd=1 then industry=11;
if corporate=1 then industry=11;

if public=1 then industry=12;

if education=1 then industry=13;

if health=1 then industry=14;

if waste=1 then industry=15;
if organizations=1 then industry=15;
if entertainment=1 then industry=15;
if drycleaning=1 then industry=15;

if households=1 then industry=16;

if foreign=1 then industry=17;

run;


proc sort data = industry_group2&f_or_i&I;
	by election&I;
run;

proc freq data = industry_group2&f_or_i&I;
	tables industry/out = ind_election&f_or_i&I noprint;
	by election&I;
run;

proc freq data = industry_group2&f_or_i&I;
	tables sector/out = sector_election&f_or_i&I noprint;
	by election&I;
run;

proc sort data = ind_election&f_or_i&I out = ind_election2&f_or_i&I;
	by election&I descending count;
run;

proc sort data = sector_election&f_or_i&I out = sector_election2&f_or_i&I;
	by election&I descending count;
run;

proc sort nodupkey data = ind_election2&f_or_i&I out = ind_election3&f_or_i&I;
	by election&I;
run;

proc sort nodupkey data = sector_election2&f_or_i&I out = sector_election3&f_or_i&I;
	by election&I;
run;

data grund.ind_election&f_or_i&I;
	set ind_election3&f_or_i&I;
		where count>1; *more than 1 individual;
	rename industry = dom_ind&f_or_i&I percent = dom_ind_percent&f_or_i&I election&I = election;
	keep election&I industry percent;
run;

data grund.sector_election&f_or_i&I;
	set sector_election3&f_or_i&I;
		where count>1; *more than 1 individual;
	rename sector = dom_sec&f_or_i&I percent = dom_sec_percent&f_or_i&I election&I = election;
	keep election&I sector percent;
run;


%end;
%mend;
%m0bbbaaaa(f);
%m0bbbaaaa(i);


/*****************/
/** Occupations **/
/*****************/

%macro m0bbbaab(f_or_i);
%do I = 1999 %to 1999;

data grund.occupations&f_or_i;			
  	 set original.linda&f_or_i&I (keep = nybidnr ssyk);
	 rename nybidnr = id;
 run;

proc sort data = grund.occupations&f_or_i;
	by id;
run;

%end;
%mend;
%m0bbbaab(f);
%m0bbbaab(i);


/*****************************************/
/********* INCOME DEFINITIONS ************/
/*****************************************/

/*different income definitions*/

%macro m0bbbaaa(f_or_i);
%do I = 1986 %to 2007;

%if &I>1994 %then %do;
	data incomedefs&f_or_i&I;			
  	 	set original.linda&f_or_i&I (keep = nybidnr cprim csfvi cbefvi ctxfvi cdispl kkap);
		rename cprim = inc_earned csfvi = inc_broad cbefvi = inc_assessed ctxfvi = inc_taxable;
		inc_dis_nocap = cdispl-max(kkap,0)*0.7;
   	run;
%end;

%if &I=1993 %then %do;
	data incomedefs&f_or_i&I;			
  	 	set original.linda&f_or_i&I (keep = nybidnr warb);
		rename warb = inc_earned;
		inc_broad =.;
		inc_assessed =.;
		inc_taxable =.;
		inc_dis_nocap =.;
   	run;
%end;

%if &I=1994 %then %do;
	data incomedefs&f_or_i&I;			
  	 	set original.linda&f_or_i&I (keep = nybidnr TTJ);
		rename TTJ = inc_earned;
		inc_broad =.;
		inc_assessed =.;
		inc_taxable =.;
		inc_dis_nocap =.;
   	run;
%end;

%if &I<1993 %then %do;
	data incomedefs&f_or_i&I;			
  	 	set original.linda&f_or_i&I (keep = nybidnr arbink);
		rename arbink = inc_earned;
		inc_broad =.;
		inc_assessed =.;
		inc_taxable =.;
		inc_dis_nocap =.;
   	run;
%end;

	proc sort data = incomedefs&f_or_i&I;
		by nybidnr;
	run;

	data incomedefs&f_or_i&I;
		merge incomedefs&f_or_i&I grund.lindaFAMILY&f_or_i&I;
			by nybidnr;
	run;

	proc sort data = incomedefs&f_or_i&I;
		by nybidnrh nybidnr;
	run;

data incomedefsFAM&f_or_i&I&f_or_i&I (keep = nybidnrh inchh_earned inchh_broad inchh_assessed inchh_taxable inchh_dis_nocap);				
   set incomedefs&f_or_i&I;
   by nybidnrh;
   if first.nybidnrh then do;
        inchh_earned=0;
		inchh_broad=0;
		inchh_assessed=0;
		inchh_taxable=0;
		inchh_dis_nocap=0;
	   end;
   		inchh_earned + inc_earned;
		inchh_broad + inc_broad;
		inchh_assessed + inc_assessed;
		inchh_taxable + inc_taxable;
		inchh_dis_nocap + inc_dis_nocap;
	if last.nybidnrh then output;
run;

data grund.incomes&f_or_i&I;
	merge incomedefs&f_or_i&I incomedefsFAM&f_or_i&I&f_or_i&I;
		by nybidnrh;
	keep nybidnr inchh_earned inchh_broad inchh_assessed inchh_taxable inc_earned inc_broad inc_assessed inc_taxable inchh_dis_nocap;
	rename nybidnr = id;
run;

proc sort data = grund.incomes&f_or_i&I;
	by id;
run;

%end;
%mend;
%m0bbbaaa(f);
%m0bbbaaa(i);


data grund.early_incf;
	merge grund.incomesf1995 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1995))
		grund.incomesf1996 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1996))
		grund.incomesf1997 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1997))
		grund.incomesf1998 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1998));
	by id;
run;

data grund.early_inci;
	merge grund.incomesi1995 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1995))
		grund.incomesi1996 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1996))
		grund.incomesi1997 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1997))
		grund.incomesi1998 (keep = id inchh_earned rename = (inchh_earned = inchh_earned1998));
	by id;
run;


**********************************************************************
**Income inequality measures (do it only for the regular LINDA sample)
**NOTE: Everything is done at the household level
**********************************************************************;

%macro m0bbbdde(f_or_i);
%do I = 1986 %to 1992;

proc sort data = grund.incomes&f_or_i&I;
	by id;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.lindafamily&f_or_i&I;
	by nybidnr;
run;

data incomemerged&f_or_i&I;
	merge grund.incomes&f_or_i&I grund.bostadsort&f_or_i&I(rename = (nybidnr = id)) grund.lindafamily&f_or_i&I(rename = (nybidnr = id));
		by id;
run;

data incomemerged_ranks&f_or_i&I;
	set incomemerged&f_or_i&I;
		where burvkodf = 1; *include only the sampled individuals (Note: can be more than one individual per household);
run;

proc means data = incomemerged_ranks&f_or_i&I;
	var inchh_earned;
run;

*income shares at the municipality level;
proc sort data = incomemerged_ranks&f_or_i&I;
	by muni&I inchh_earned;
run;

proc means data = incomemerged_ranks&f_or_i&I noprint;
	class muni&I;
		var inchh_earned;
	where burvkodf = 1;
	output out = avg_inc_muni&I mean = mean_inc_muni&I;
run;

data grund.avg_inc_muni&I;
	set avg_inc_muni&I;
		where muni&I>'0';
	keep muni&I mean_inc_muni&I;
run;

proc rank data = incomemerged_ranks&f_or_i&I groups = 10 out = income_ranks_muni&f_or_i&I;
	var inchh_earned;
		by muni&I;
			ranks p_inc_earned;
run;

*aggregate income per income decile and municipality;
data inc_muni&f_or_i&I (keep = muni&I p_inc_earned tot_inc_earned num);
	set income_ranks_muni&f_or_i&I;
		by muni&I p_inc_earned;
			retain tot_inc_earned 0;
				if first.p_inc_earned then do;
					tot_inc_earned = 0;
					num = 0;
				end;

				tot_inc_earned = tot_inc_earned + inchh_earned;
				num + 1;
				
				if last.p_inc_earned then do;
					output;
				end;
run;

*aggregate income per municiaplity;
data inc_agg_muni&f_or_i&I (keep = muni&I tot_inc_muni num_muni);
	set income_ranks_muni&f_or_i&I;
		by muni&I;
			retain tot_inc_muni 0;
				if first.muni&I then do;
					tot_inc_muni = 0;
					num_muni = 0;
				end;

				tot_inc_muni = tot_inc_muni + inchh_earned;
				num_muni + 1;
				
				if last.muni&I then do;
					output;
				end;
run;

*estimate income shares per decile and municipality;
proc sort data = grund.bostadsortf&I;
	by muni&I;
run;

proc sort data = grund.bostadsorti&I;
	by muni&I;
run;

data inc_top10_muni&f_or_i&I;
	merge inc_agg_muni&f_or_i&I inc_muni&f_or_i&I;
		by muni&I;
	if num_muni>20 and tot_inc_muni>0 then inc_top10_muni&I = tot_inc_earned/tot_inc_muni;
run;

data grund.inc_top10_muni&I(keep = muni&I inc_top10_muni&I);
	set inc_top10_muni&f_or_i&I;
		where p_inc_earned = 9 and muni&I>'0'; *keep only top decile (top 10);
run;

proc means data = grund.inc_top10_muni&I;
	var inc_top10_muni&I;
run;

%end;
%mend;
%m0bbbdde(f);


****************************************
**Income mobility at municipality level
***************************************;

*Income mobility between 1987 and 1992;
proc sort nodupkey data = income_ranks_munif1987;
	by id;
run;

proc sort nodupkey data = income_ranks_munif1992;
	by id;
run;

data inc_mobility;
	merge income_ranks_munif1987(keep = id p_inc_earned muni1987) income_ranks_munif1992(keep = id p_inc_earned muni1992 rename = (p_inc_earned = p_inc_earned1992));
		by id;
run;

data inc_mobility;
	set inc_mobility;
		where muni1987>'0' and muni1987=muni1992;
run;

data inc_mobility;
	set inc_mobility;
		where p_inc_earned = 9;
	if p_inc_earned1992 = 9 then mobility = 1; else mobility = 0;
run;

proc means data = inc_mobility;
	var mobility;
run;

proc sort data = inc_mobility;
	by muni1987;
run;

proc freq data = inc_mobility;
	table mobility/out = inc_mob outpercent;
		by muni1987;
run;

data inc_mob;
	set inc_mob;
		where mobility = 1;
run;

proc sort data = inc_mob;
	by percent;
run;

data grund.inc_mob;
	set inc_mob;
		rename muni1987=muni count = no_mob percent = incmob;
		keep muni1987 count percent;
run;

proc sort data = grund.inc_mob;
	by muni;
run;

proc univariate data = grund.inc_mob;
	var incmob;
run;
******************************************;

%macro m0bbbddeff(f_or_i);
%do I = 1987 %to 1992;

data inc_mob&I;
	set grund.inc_mob;
		rename muni = muni&I incmob = incmob&I;
run;

*include the municipality measures to individual data (for the regular and immigrant samples);
data grund.inc_muni_indf&I;
	merge grund.inc_top10_muni&I grund.avg_inc_muni&I inc_mob&I grund.bostadsortf&I(in = in_bostad);
		by muni&I;
			if muni&I>'0' and in_bostad = 1;
				keep nybidnr inc_top10_muni&I mean_inc_muni&I incmob&I;
					rename nybidnr = id;
run;

data grund.inc_muni_indi&I;
	merge grund.inc_top10_muni&I grund.avg_inc_muni&I inc_mob&I grund.bostadsorti&I(in = in_bostad);
		by muni&I;
			if muni&I>'0' and in_bostad = 1;
				keep nybidnr inc_top10_muni&I mean_inc_muni&I incmob&I;
					rename nybidnr = id;
run;

proc sort data = grund.inc_muni_indf&I;
	by id;
run;

proc sort data = grund.inc_muni_indi&I;
	by id;
run;

proc sort data = grund.bostadsortf&I;
	by nybidnr;
run;

proc sort data = grund.bostadsorti&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbddeff(f);


*aggregate data for the years 1987 to 1992;
data grund.inc_muni_indf;
	merge grund.inc_muni_indf1987-grund.inc_muni_indf1992;
		by id;
run;

data grund.inc_muni_indi;
	merge grund.inc_muni_indi1987-grund.inc_muni_indi1992;
		by id;
run;

proc means data = grund.inc_muni_indf;
run;

proc means data = grund.inc_muni_indi;
run;


*************************************************;
*Unemployment benefits;
*************************************************;

%macro m0bbbc(f_or_i);
%do I = 1986 %to 2007;

%if &I<1991 %then %do;
data grund.unemployment&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr dagparb);
	unemploybenefits&I = dagparb;							* Unemployment benefits;
		label unemploybenefits&I = 'Unemployment benefits';
	drop dagparb;
	rename nybidnr = id;
run;
%end;

%else %do;
data grund.unemployment&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr tarbstl);
	unemploybenefits&I = tarbstl;							* Unemployment benefits;
		label unemploybenefits&I = 'Unemployment benefits';
	drop tarbstl;
	rename nybidnr = id;
run;
%end;

proc sort data = grund.unemployment&f_or_i&I;
	by id;
run;
%end;

data grund.unemployment&f_or_i;
	merge grund.unemployment&f_or_i.1987-grund.unemployment&f_or_i.2007;
	by id;
run;

%mend;
%m0bbbc(f);
%m0bbbc(i);


*************************************************;
**welfare benefits**;
*************************************************;

%macro m0bbbaacc(f_or_i);
%do I = 1999 %to 2007;

data welfare&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf isocbid bsocman tsocers);
run;

proc sort data = welfare&f_or_i&I;
	by nybidnrf;
run;

data welfareFAM&f_or_i&I(keep = nybidnrf welfare_fam);				
   set welfare&f_or_i&I;
   	by nybidnrf;
   if first.nybidnrf then do;
        welfare_fam=0;
	end;
   		welfare_fam + isocbid;
	if last.nybidnrf then output;
run;

data grund.welfare&f_or_i&I;
	merge welfareFAM&f_or_i&I welfare&f_or_i&I;
		by nybidnrf;
	rename isocbid = welfare nybidnr = id;
	keep nybidnr welfare_fam isocbid;
run;

proc sort data = grund.welfare&f_or_i&I;
	by id;
run;

%end;
%mend;
%m0bbbaacc(f);
%m0bbbaacc(i);


*************************************************;
** Interest and dividend payments			   **;
*************************************************;

%macro m0bbbaacc(f_or_i);

data aktieutdel&f_or_i.1988;
	set original.linda&f_or_i.1988 (keep = nybidnr autdel);
run;

proc sort data = aktieutdel&f_or_i.1988;
	by nybidnr;
run;

%do I = 1986 %to 2007;

%if &I<1988 %then %do;
data dividend_temp&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrh INRTA);
run;

proc sort data = dividend_temp&f_or_i&I;
	by nybidnr;
run;

data dividend&f_or_i&I;
	merge dividend_temp&f_or_i&I(in=div) aktieutdel&f_or_i.1988; *dividend income is only available from 1988;
		by nybidnr;
			if div = 1;
run;

data dividend&f_or_i&I;
	set dividend&f_or_i&I;
		if autdel =. then autdel = 0; *set missing values to 0;
run;

%end;

%if &I>1987 and &I<1991 %then %do;
data dividend&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrh INRTA autdel);
run;
%end;

%if &I>1990 and &I<1993 %then %do;
data dividend&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf KAPRTA autdel);
		rename KAPRTA = INRTA nybidnrf = nybidnrh;
run;
%end;

%if &I=1993 %then %do;
data dividend&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf KIRANTA KKUAKTI);
		rename KIRANTA = INRTA KKUAKTI = autdel nybidnrf = nybidnrh;
run;
%end;

%if &I>1993 %then %do;
data dividend&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf KIRANTA KKUUTD);
		rename KIRANTA = INRTA KKUUTD = autdel nybidnrf = nybidnrh;
run;
%end;

proc sort data = dividend&f_or_i&I;
	by nybidnrh;
run;

data dividendFAM&f_or_i&I(keep = nybidnrh hhdividend hhdivinc);				
   set dividend&f_or_i&I;
   	by nybidnrh;
   if first.nybidnrh then do;
        hhdividend=0;
		hhdivinc=0;
	end;
   		hhdividend + INRTA;
		hhdivinc + autdel;
	if last.nybidnrh then output;
run;

data grund.dividend&f_or_i&I;
	merge dividendFAM&f_or_i&I dividend&f_or_i&I;
		by nybidnrh;
	rename INRTA = dividend autdel = divinc;
	keep nybidnr hhdividend hhdivinc INRTA autdel;
run;

proc sort data = grund.dividend&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbaacc(f);


**************************************************;
** Capital income								**;
**************************************************;

%macro m0bbbaacc(f_or_i);
%do I = 1986 %to 2007;

%if &I<1991 %then %do;
data capinc&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrh INKAP);
		rename INKAP = capinc;
run;
%end;

%if &I>1990 and &I<1993 %then %do;
data capinc&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf INKAP);
		rename INKAP = capinc nybidnrf = nybidnrh;
run;
%end;

%if &I>1992 %then %do;
data capinc&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf KKAP);
		rename KKAP = capinc nybidnrf = nybidnrh;
run;
%end;

%if &I<1994 %then %do;
data capinc&f_or_i&I;
 	set capinc&f_or_i&I;
		capinc = capinc*100;
%end;

proc sort data = capinc&f_or_i&I;
	by nybidnrh;
run;

data capincFAM&f_or_i&I(keep = nybidnrh hhcapinc);				
   set capinc&f_or_i&I;
   	by nybidnrh;
   if first.nybidnrh then do;
        hhcapinc=0;
	end;
   		hhcapinc + capinc;
	if last.nybidnrh then output;
run;

data grund.capinc&f_or_i&I;
	merge capincFAM&f_or_i&I capinc&f_or_i&I;
		by nybidnrh;
	keep nybidnr hhcapinc capinc;
run;

proc sort data = grund.capinc&f_or_i&I;
	by nybidnr;
run;

proc means data = grund.capinc&f_or_i&I;
	var hhcapinc capinc;
run;

%end;
%mend;
%m0bbbaacc(f);


**************************************************;
** Interest payments							**;
**************************************************;

%macro m0bbbaacc(f_or_i);
%do I = 1999 %to 2007;

data interest&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr nybidnrf kakurta kasku);
run;

proc sort data = interest&f_or_i&I;
	by nybidnrf;
run;

data interestFAM&f_or_i&I(keep = nybidnrf interest_fam interest_fam_alt);				
   set interest&f_or_i&I;
   	by nybidnrf;
   if first.nybidnrf then do;
        interest_fam=0;
		interest_fam_alt = 0;
	end;
   		interest_fam + kakurta;
		interest_fam_alt + kasku;
	if last.nybidnrf then output;
run;

data grund.interest&f_or_i&I;
	merge interestFAM&f_or_i&I interest&f_or_i&I;
		by nybidnrf;
	rename nybidnr = id;
	keep nybidnr interest_fam interest_fam_alt;
run;

proc sort data = grund.interest&f_or_i&I;
	by id;
run;

%end;
%mend;
%m0bbbaacc(f);
%m0bbbaacc(i);


**************************************************;
** Property tax 								**;
**************************************************;

%macro m0bbbaaae(f_or_i);
%do I = 1986 %to 2007;

%if &I<1991 %then %do;
data propertytax&f_or_i&I;
	set original.linda&f_or_i&I(keep = nybidnr UFASTS FASTSK);
	rename UFASTS = property_value FASTSK = property_tax;
run;
%end;

%if &I>1990 and &I<1993 %then %do;
data propertytax&f_or_i&I;
	set original.linda&f_or_i&I(keep = nybidnr UFASTS1 UFASTS2 UFASTS3 SKFAST);
	UFASTS = sum(of UFASTS1 UFASTS2 UFASTS3);
	rename UFASTS = property_value SKFAST = property_tax;
run;
%end;

%if &I>1992 %then %do;
data propertytax&f_or_i&I;
	set original.linda&f_or_i&I(keep = nybidnr SFAST);
	property_value = 1;
	rename SFAST = property_tax;
run;
%end;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

proc sort data = propertytax&f_or_i&I;
	by nybidnr;
run;

data propertytax_m&f_or_i&I;
	merge propertytax&f_or_i&I grund.bostadsort&f_or_i&I grund.lindafamily&f_or_i&I;
		by nybidnr;
run;

proc sort data = propertytax_m&f_or_i&I;
	by nybidnrh;
run;

data propertytax_fam&f_or_i&I;				
   set propertytax_m&f_or_i&I;
   	by nybidnrh;
   if first.nybidnrh then do;
        property_value_f=0;
		property_tax_f=0;
	end;
   		property_value_f + property_value;
		property_tax_f + property_tax;
	if last.nybidnrh then output;
run;

data grund.propertytax_fam&f_or_i&I;
	set propertytax_fam&f_or_i&I;
	keep nybidnrh property_value_f property_tax_f;
run;

data grund.propertytax_ind&f_or_i&I (keep = nybidnr property_value_f property_tax_f property_value property_tax);
	merge propertytax_m&f_or_i&I grund.propertytax_fam&f_or_i&I;
		by nybidnrh;
run;

proc sort data = grund.propertytax_ind&f_or_i&I;
	by nybidnr;
run;

proc sort data = propertytax_fam&f_or_i&I;
	by election&I;
run;

proc means data = propertytax_fam&f_or_i&I noprint;
	class election&I;
		var property_value_f;
	output out = avg_property_elect&I median = avg_property_elect&I p75 = p75_property_elect&I;
run;

data grund.avg_property_elect&I;
	set avg_property_elect&I;
		where election&I>'0';
		rename election&I = election;
		keep election&I avg_property_elect&I p75_property_elect&I;
run;

%end;
%mend;
%m0bbbaaae(f);


***********************************************************************************************************************
** Estimate average wealth per electoral district, parish and municipality (do it only for the regular LINDA sample) **
***********************************************************************************************************************;

*Variable: FORMT (Familjens f�rm�genhet (inkl barns f�rm));
*NOTE: wealth can be negative in 1991 and 1992;

%macro m0bbbaaad(f_or_i);
%do I = 1986 %to 2007;

%if 1992<&I and &I<1999 %then %do;
data wealth&f_or_i&I;
	set original.linda&f_or_i&I(keep = nybidnr FTOTPOS SFORM);
		rename FTOTPOS = FORMT SFORM = SKFORM;
run;

data grund.taxwealth&f_or_i&I;
	set wealth&f_or_i&I;
		rename FORMT = hhwealth&I SKFORM = wealthtax&I nybidnr = id;
run;

data grund.taxwealthi&I; *immigrant sample;
	set original.lindai&I(keep = nybidnr FTOTPOS SFORM);
		rename FTOTPOS = hhwealth&I SFORM = wealthtax&I nybidnr = id;
run;
%end;

%if &I>1998 %then %do;
data wealth&f_or_i&I;
	set main.linda&f_or_i&I(keep = id hhnetw);
		rename hhnetw = FORMT id = nybidnr;
run;

data grund.taxwealth&f_or_i&I;
	set wealth&f_or_i&I;
		rename FORMT = hhwealth&I nybidnr = id;
run;

data grund.taxwealthi&I;
	set  main.lindai&I(keep = id hhnetw);
		rename hhnetw = hhwealth&I;
run;

%end;

%if &I<1993 %then %do;
data wealth&f_or_i&I;
	set original.linda&f_or_i&I(keep = nybidnr FORMT SKFORM);
run;

data grund.taxwealth&f_or_i&I;
	set wealth&f_or_i&I;
		rename FORMT = hhwealth&I SKFORM = wealthtax&I nybidnr = id;
run;

data grund.taxwealthi&I; *immigrant sample;
	set original.lindai&I(keep = nybidnr FORMT SKFORM);
		rename FORMT = hhwealth&I SKFORM = wealthtax&I nybidnr = id;
run;
%end;


proc sort data = grund.taxwealth&f_or_i&I;
	by id;
run;

proc sort data = grund.taxwealthi&I;
	by id;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

proc sort data = wealth&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.propertytax_ind&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.dividend&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.capinc&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.headedu&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.entreprenourfam&f_or_i&I;
	by id;
run;

data wealth_merged&f_or_i&I;
	merge wealth&f_or_i&I grund.bostadsort&f_or_i&I grund.lindafamily&f_or_i&I grund.age&f_or_i&I grund.propertytax_ind&f_or_i&I grund.dividend&f_or_i&I grund.capinc&f_or_i&I grund.headedu&f_or_i&I grund.entreprenourfam&f_or_i&I(rename=(id=nybidnr));
		by nybidnr;
			if eduuni_max =. then eduuni_max = 0; *eduuni_max can be missing;
			if selfemployed_hh>0 then hhselfemp = 1; else hhselfemp = 0;
			if hhdivinc>0 then hhdiv = 1; else hhdiv = 0;
			if property_tax_f>0 then hhprop = 1; else hhprop = 0;
			if closely_held_firm_hh>0 then hhchf = 1; else hhchf = 0;
run;

proc univariate data = wealth_merged&f_or_i&I;
	var FORMT;
run;

proc means data = wealth_merged&f_or_i&I;
	var hhselfemp hhdiv hhprop hhchf;
run;

proc univariate data = wealth_merged&f_or_i&I;
	var FORMT;
		where burvkodf = 1; *include only sampled individuals;
run;

data wealth_merged_ranks&f_or_i&I;
	set wealth_merged&f_or_i&I;
		where burvkodf = 1; *NOTE: keep only the sampled individual;
run;


***************************;
*At election district level;
***************************;
proc sort data = wealth_merged&f_or_i&I;
	by election&I;
run;

proc means data = wealth_merged&f_or_i&I noprint;
	class election&I;
		var FORMT;
	where burvkodf = 1; 
	output out = avg_wealth_elect&I median = avg_wealth_elect&I p75 = p75_wealth_elect&I;
run;

data grund.avg_wealth_elect&I;
	set avg_wealth_elect&I;
		where election&I>'0';
			rename election&I = election;
		keep election&I avg_wealth_elect&I p75_wealth_elect&I;
run;

proc univariate data = wealth_merged&f_or_i&I noprint;
	var FORMT; 
		class election&I;
			where burvkodf = 1;
		output out=avg_wealth_electnew&I pctlpre= wealth_electp pctlpts = 10,20,25,50,90,95;
run;

data grund.avg_wealth_electnew&I;
	set avg_wealth_electnew&I;
		where election&I>'0';
		keep election&I wealth_electp50 wealth_electp90 wealth_electp95;
		rename wealth_electp50 = p50_wealth_elect&I wealth_electp90 = p90_wealth_elect&I wealth_electp95 = p95_wealth_elect&I election&I = election;
run;

*at parish level;
proc sort data = wealth_merged&f_or_i&I;
	by parish&I;
run;

proc means data = wealth_merged&f_or_i&I noprint;
	class parish&I;
		var FORMT;
	where burvkodf = 1;
	output out = avg_wealth_parish&I median = avg_wealth_parish&I p75 = p75_wealth_parish&I;
run;

data grund.avg_wealth_parish&I;
	set avg_wealth_parish&I;
		where parish&I>'0';
		keep parish&I avg_wealth_parish&I p75_wealth_parish&I;
run;

proc univariate data = wealth_merged&f_or_i&I noprint;
	var FORMT; 
		class parish&I;
			where burvkodf = 1;
		output out=avg_wealth_parishnew&I pctlpre= wealth_parishp pctlpts = 20,50,90,95;
run;

data grund.avg_wealth_parishnew&I;
	set avg_wealth_parishnew&I;
		where parish&I>'0';
		keep parish&I wealth_parishp50 wealth_parishp90 wealth_parishp95;
		rename wealth_parishp50 = p50_wealth_parish&I wealth_parishp90 = p90_wealth_parish&I wealth_parishp95 = p95_wealth_parish&I;
run;

*wealth shares;
proc sort data = wealth_merged_ranks&f_or_i&I;
	by parish&I FORMT;
run;

proc rank data = wealth_merged_ranks&f_or_i&I groups = 10 out = wealth_ranks_parish&f_or_i&I;
	var FORMT;
	by parish&I;
		ranks p_wealth;
run;

data wealth_ranks_parish&f_or_i&I;
	set wealth_ranks_parish&f_or_i&I;
		if FORMT<0 then FORMT = 0;
run;

data wealth_parish&f_or_i&I (keep = parish&I p_wealth tot_wealth num);
	set wealth_ranks_parish&f_or_i&I;
	by parish&I p_wealth;
	retain tot_wealth 0;
	if first.p_wealth then do;
		tot_wealth = 0;
		num = 0;
		end;

	tot_wealth = tot_wealth + FORMT;
	num + 1;
	
	if last.p_wealth then do;
		output;
	end;
run;

data wealth_agg_parish&f_or_i&I (keep = parish&I tot_wealth_parish num_parish);
	set wealth_ranks_parish&f_or_i&I;
	by parish&I;
	retain tot_wealth_parish 0;
	if first.parish&I then do;
		tot_wealth_parish = 0;
		num_parish = 0;
		end;

	tot_wealth_parish = tot_wealth_parish + FORMT;
	num_parish + 1;
	
	if last.parish&I then do;
		output;
	end;
run;

data wealth_top10_parish&f_or_i&I;
	merge wealth_agg_parish&f_or_i&I wealth_parish&f_or_i&I;
		by parish&I;
	if num_parish>20 and tot_wealth_parish>0 then share_top10_par_&I = tot_wealth/tot_wealth_parish;
run;

data grund.wealth_top10_parish&I(keep = parish&I share_top10_par_&I);
	set wealth_top10_parish&f_or_i&I;
		where p_wealth = 9 and parish&I>'0';
run;

data grund.wealth_top20_parish&I(keep = parish&I share_top1020_par_&I);
	set wealth_top10_parish&f_or_i&I;
		where p_wealth = 8 and parish&I>'0';
	rename share_top10_par_&I = share_top1020_par_&I;
run;


**************************;
*At the municipality level;
**************************;

proc sort data = wealth_merged&f_or_i&I;
	by muni&I;
run;

proc means data = wealth_merged&f_or_i&I noprint;
	class muni&I;
		var FORMT;
	where burvkodf = 1; *keep only sampled individuals;
	output out = avg_wealth_muni&I median = avg_wealth_muni&I p75 = p75_wealth_muni&I;
run;

data grund.avg_wealth_muni&I;
	set avg_wealth_muni&I;
		where muni&I>'0';
	keep muni&I avg_wealth_muni&I p75_wealth_muni&I;
run;

proc univariate data = wealth_merged&f_or_i&I noprint;
	var FORMT; 
		class muni&I;
			where burvkodf = 1;
		output out=avg_wealth_muninew&I pctlpre= wealth_munip pctlpts = 20,50,90,95;
run;

data grund.avg_wealth_muninew&I;
	set avg_wealth_muninew&I;
		where muni&I>'0';
		keep muni&I wealth_munip50 wealth_munip90 wealth_munip95;
		rename wealth_munip50 = p50_wealth_muni&I wealth_munip90 = p90_wealth_muni&I wealth_munip95 = p95_wealth_muni&I;
run;

*wealth shares;
proc sort data = wealth_merged_ranks&f_or_i&I;
	by muni&I FORMT;
run;

proc rank data = wealth_merged_ranks&f_or_i&I groups = 10 out = wealth_ranks_muni&f_or_i&I;
	var FORMT;
		by muni&I;
			ranks p_wealth;
run;

data wealth_ranks_muni&f_or_i&I;
	set wealth_ranks_muni&f_or_i&I;
		if FORMT<0 then FORMT = 0;
run;

data grund.wealth_ranks_muni&f_or_i&I;
	set wealth_ranks_muni&f_or_i&I;
run;

proc means data = wealth_ranks_muni&f_or_i&I noprint;
	class muni&I;
		var FORMT;
	output out = mean_wealth_muni&I mean = mean_wealth_muni&I;
run;

data grund.mean_wealth_muni&I;
	set mean_wealth_muni&I;
		where muni&I>'0';
	keep muni&I mean_wealth_muni&I;
run;

data wealth_agg&f_or_i&I (keep = muni&I p_wealth tot_wealth tot_proptax tot_propval tot_div tot_eduuni tot_divear tot_propown tot_selfemp num);
	set wealth_ranks_muni&f_or_i&I;
		by muni&I p_wealth;
			retain tot_wealth 0 tot_proptax 0 tot_propval 0 tot_div 0 tot_eduuni 0 tot_divear 0 tot_propown 0 tot_selfemp 0;
				if first.p_wealth then do;
					tot_wealth = 0;
					tot_proptax = 0;
					tot_propval = 0;
					tot_div = 0;
					tot_eduuni = 0;
					tot_divear = 0;
					tot_propown = 0;
					tot_selfemp = 0;
					num = 0;
				end;

				tot_wealth = tot_wealth + FORMT;
				tot_proptax = tot_proptax + property_tax_f;
				tot_propval = tot_propval + property_value_f;
				tot_div = tot_div + hhdividend;
				tot_eduuni = tot_eduuni + eduuni_max;
				tot_divear = tot_divear + hhdiv;
				tot_propown = tot_propown + hhprop;
				tot_selfemp = tot_selfemp + hhselfemp;
				num + 1;
	
				if last.p_wealth then do;
					output;
				end;
run;

*aggregate at the municipality level;
data wealth_agg_muni&f_or_i&I (keep = muni&I tot_wealth_muni tot_ptax_muni tot_pval_muni tot_div_muni num_muni);
	set wealth_ranks_muni&f_or_i&I;
		by muni&I;
			retain tot_wealth_muni 0 tot_ptax_muni 0 tot_div_muni 0 tot_pval_muni 0;
				if first.muni&I then do;
					tot_wealth_muni = 0;
					tot_ptax_muni = 0;
					tot_pval_muni = 0;
					tot_div_muni = 0;
					num_muni = 0;
				end;

				tot_wealth_muni = tot_wealth_muni + FORMT;
				tot_ptax_muni = tot_ptax_muni + property_tax_f;
				tot_pval_muni = tot_pval_muni + property_value_f;
				tot_div_muni = tot_div_muni + hhdividend;
				num_muni + 1;
				
				if last.muni&I then do;
					output;
				end;
run;

data wealth_top10_muni&f_or_i&I;
	merge wealth_agg_muni&f_or_i&I wealth_agg&f_or_i&I;
		by muni&I;
			if num_muni>20 then	share_top10_&I = tot_wealth/tot_wealth_muni;
			if num_muni>20 then	ptax_top10_&I = tot_proptax/tot_ptax_muni;
			if num_muni>20 then	pval_top10_&I = tot_propval/tot_pval_muni;
			if num_muni>20 then	div_top10_&I = tot_div/tot_div_muni;
			if num_muni>20 then uni_top10_&I = tot_eduuni/num;
			if num_muni>20 then propo_top10_&I = tot_propown/num;
			if num_muni>20 then divea_top10_&I = tot_divear/num;
			if num_muni>20 then self_top10_&I = tot_selfemp/num;
run;

data grund.wealth_top10_muni&I(keep = muni&I share_top10_&I ptax_top10_&I pval_top10_&I div_top10_&I uni_top10_&I propo_top10_&I divea_top10_&I self_top10_&I);
	set wealth_top10_muni&f_or_i&I;
		where p_wealth = 9 and muni&I>'0'; *include only the top decile;
run;

data grund.wealth_top20_muni&I(keep = muni&I share_top1020_&I);
	set wealth_top10_muni&f_or_i&I;
		where p_wealth = 8 and muni&I>'0';
			rename share_top10_&I = share_top1020_&I; *include only the second decile;
run;


**Sort by household dividend;
proc sort data = wealth_merged_ranks&f_or_i&I;
	by muni&I hhdividend;
run;

proc rank data = wealth_merged_ranks&f_or_i&I groups = 10 out = div_ranks_muni&f_or_i&I;
	var hhdividend;
	by muni&I;
		ranks p_div;
run;

data div_agg&f_or_i&I (keep = muni&I p_div tot_div num);
	set div_ranks_muni&f_or_i&I;
	by muni&I p_div;
	retain tot_div 0;
	if first.p_div then do;
		tot_div = 0;
		num = 0;
		end;

	tot_div = tot_div + hhdividend;
	num + 1;
	
	if last.p_div then do;
		output;
	end;
run;

data div_agg_muni&f_or_i&I (keep = muni&I tot_div_muni num_muni);
	set div_ranks_muni&f_or_i&I;
	by muni&I;
	retain tot_div_muni 0;
	if first.muni&I then do;
		tot_div_muni = 0;
		num_muni = 0;
		end;

	tot_div_muni = tot_div_muni + hhdividend;
	num_muni + 1;
	
	if last.muni&I then do;
		output;
	end;
run;

data div_top10_muni&f_or_i&I;
	merge div_agg_muni&f_or_i&I div_agg&f_or_i&I;
		by muni&I;
	if num_muni>20 then	sdiv_top10_&I = tot_div/tot_div_muni;
run;

data grund.div_top10_muni&I(keep = muni&I sdiv_top10_&I);
	set div_top10_muni&f_or_i&I;
		where p_div = 9 and muni&I>'0';
run;

**Sort by property value;
proc sort data = wealth_merged_ranks&f_or_i&I;
	by muni&I property_value_f;
run;

proc rank data = wealth_merged_ranks&f_or_i&I groups = 10 out = pval_ranks_muni&f_or_i&I;
	var property_value_f;
	by muni&I;
		ranks p_pval;
run;

data pval_agg&f_or_i&I (keep = muni&I p_pval tot_pval num);
	set pval_ranks_muni&f_or_i&I;
	by muni&I p_pval;
	retain tot_pval 0;
	if first.p_pval then do;
		tot_pval = 0;
		num = 0;
		end;

	tot_pval = tot_pval + property_value_f;
	num + 1;
	
	if last.p_pval then do;
		output;
	end;
run;

data pval_agg_muni&f_or_i&I (keep = muni&I tot_pval_muni num_muni);
	set pval_ranks_muni&f_or_i&I;
	by muni&I;
	retain tot_pval_muni 0;
	if first.muni&I then do;
		tot_pval_muni = 0;
		num_muni = 0;
		end;

	tot_pval_muni = tot_pval_muni + property_value_f;
	num_muni + 1;
	
	if last.muni&I then do;
		output;
	end;
run;

data pval_top10_muni&f_or_i&I;
	merge pval_agg_muni&f_or_i&I pval_agg&f_or_i&I;
		by muni&I;
	if num_muni>20 then	spval_top10_&I = tot_pval/tot_pval_muni;
run;

data grund.pval_top10_muni&I(keep = muni&I spval_top10_&I);
	set pval_top10_muni&f_or_i&I;
		where p_pval = 9 and muni&I>'0';
run;

**Sort by property tax;
proc sort data = wealth_merged_ranks&f_or_i&I;
	by muni&I property_tax_f;
run;

proc rank data = wealth_merged_ranks&f_or_i&I groups = 10 out = ptax_ranks_muni&f_or_i&I;
	var property_tax_f;
	by muni&I;
		ranks p_ptax;
run;

data ptax_agg&f_or_i&I (keep = muni&I p_ptax tot_ptax num);
	set ptax_ranks_muni&f_or_i&I;
	by muni&I p_ptax;
	retain tot_ptax 0;
	if first.p_ptax then do;
		tot_ptax = 0;
		num = 0;
		end;

	tot_ptax = tot_ptax + property_tax_f;
	num + 1;
	
	if last.p_ptax then do;
		output;
	end;
run;

data ptax_agg_muni&f_or_i&I (keep = muni&I tot_ptax_muni num_muni);
	set ptax_ranks_muni&f_or_i&I;
	by muni&I;
	retain tot_ptax_muni 0;
	if first.muni&I then do;
		tot_ptax_muni = 0;
		num_muni = 0;
		end;

	tot_ptax_muni = tot_ptax_muni + property_tax_f;
	num_muni + 1;
	
	if last.muni&I then do;
		output;
	end;
run;

data ptax_top10_muni&f_or_i&I;
	merge ptax_agg_muni&f_or_i&I ptax_agg&f_or_i&I;
		by muni&I;
	if num_muni>20 then	sptax_top10_&I = tot_ptax/tot_ptax_muni;
run;

data grund.ptax_top10_muni&I(keep = muni&I sptax_top10_&I);
	set ptax_top10_muni&f_or_i&I;
		where p_ptax = 9 and muni&I>'0';
run;

*********************************************************************************************************
*add ID variable for the wealth data at the parish level (both for the regular and the immigrant samples)
*********************************************************************************************************;

proc sort data = grund.bostadsortf&I;
	by parish&I;
run;

proc sort data = grund.bostadsorti&I;
	by parish&I;
run;

*regular sample;
data grund.avg_wealth_parish_indf&I;
	merge grund.avg_wealth_parish&I grund.avg_wealth_parishnew&I grund.wealth_top10_parish&I grund.wealth_top20_parish&I grund.bostadsortf&I(in = in_bostad);
		by parish&I;
			if parish&I>'0' and in_bostad = 1;
	keep nybidnr avg_wealth_parish&I p50_wealth_parish&I p75_wealth_parish&I p90_wealth_parish&I p95_wealth_parish&I share_top10_par_&I share_top1020_par_&I;
	rename nybidnr = id;
run;

*immigrant sample;
data grund.avg_wealth_parish_indi&I;
	merge grund.avg_wealth_parish&I grund.avg_wealth_parishnew&I grund.wealth_top10_parish&I grund.wealth_top20_parish&I grund.bostadsorti&I(in = in_bostad);
		by parish&I;
			if parish&I>'0' and in_bostad = 1;
	keep nybidnr avg_wealth_parish&I p50_wealth_parish&I p75_wealth_parish&I p90_wealth_parish&I p95_wealth_parish&I share_top10_par_&I share_top1020_par_&I;
	rename nybidnr = id;
run;

proc sort data = grund.avg_wealth_parish_indi&I;
	by id;
run;

proc sort data = grund.avg_wealth_parish_indf&I;
	by id;
run;

%end;
%mend;
%m0bbbaaad(f);


****************************************
**Wealth mobility at municipality level
***************************************;

*Wealth mobility between 1986 and 1992;
proc sort nodupkey data = wealth_ranks_munif1986;
	by nybidnr;
run;

proc sort nodupkey data = wealth_ranks_munif1992;
	by nybidnr;
run;

data mobility;
	merge wealth_ranks_munif1986(keep = nybidnr p_wealth muni1986) wealth_ranks_munif1992(keep = nybidnr p_wealth muni1992 eduuni_max rename = (p_wealth = p_wealth1992));
		by nybidnr;
run;

data mobility;
	set mobility;
		where muni1986>'0' and muni1986=muni1992; *just include stayers in the municipality;
run;

****************************************************************************************************************;
proc freq data = mobility;
	table p_wealth;
run;

proc freq data = mobility;
	table p_wealth1992;
run;

data mobility_test;
	set mobility;

	if p_wealth<9 and p_wealth1992 = 9 and eduuni_max = 1 then mob_highedu = 1;
	if p_wealth<9 and p_wealth1992 < 9 and eduuni_max = 1 then mob_highedu = 0;

	if p_wealth<9 and p_wealth1992 = 9 and eduuni_max = 0 then mob_lowedu = 1;
	if p_wealth<9 and p_wealth1992 < 9 and eduuni_max = 0 then mob_lowedu = 0;

	if p_wealth<9 and p_wealth1992 = 9 then mob = 1;
	if p_wealth<9 and p_wealth1992 < 9 then mob = 0;

	if p_wealth=9 and p_wealth1992 = 9 then mob_old = 1;
	if p_wealth=9 and p_wealth1992 < 9 then mob_old = 0;
run;

proc sort data = mobility_test;
	by muni1986;
run;

proc freq data = mobility_test;
	table mob_highedu/out = wealth_mob_high outpercent;
		by muni1986;
run;

proc freq data = mobility_test;
	table mob_lowedu/out = wealth_mob_low outpercent;
		by muni1986;
run;

data wealth_mob_low;
	set wealth_mob_low;
		where mob_lowedu = 1;
run;

proc sort data = wealth_mob_low;
	by percent;
run;

data wealth_mob_high;
	set wealth_mob_high;
		where mob_highedu = 1;
run;

proc sort data = wealth_mob_high;
	by percent;
run;

data wealth_mob_high2;
	set wealth_mob_high;
		rename muni1986=muni count = no_mob_high percent = upmob_high;
		keep muni1986 count percent;
run;

data wealth_mob_low2;
	set wealth_mob_low;
		rename muni1986=muni count = no_mob_low percent = upmob_low;
		keep muni1986 count percent;
run;

proc sort data = wealth_mob_high2;
	by muni;
run;

proc sort data = wealth_mob_low2;
	by muni;
run;

******************************************************************************;
data mobility;
	set mobility;
		where p_wealth = 9;
	if p_wealth1992 = 9 then mobility = 1; else mobility = 0;
run;

proc sort data = mobility;
	by muni1986;
run;

proc freq data = mobility;
	table mobility/out = wealth_mob outpercent;
		by muni1986;
run;

data wealth_mob;
	set wealth_mob;
		where mobility = 1;
run;

proc sort data = wealth_mob;
	by percent;
run;

data grund.wealth_mob;
	set wealth_mob;
		rename muni1986=muni count = no_mob percent = topmob;
		keep muni1986 count percent;
run;

proc sort data = grund.wealth_mob;
	by muni;
run;

proc univariate data = grund.wealth_mob;
	var topmob;
run;

data grund.wealth_mob_complete;
	merge grund.wealth_mob wealth_mob_high2 wealth_mob_low2;
	by muni;
run;

proc corr data = grund.wealth_mob_complete;
	var topmob upmob_low upmob_high;
run;

**Wealth mobility for each year (year-to-year changes);
data wealth_ranks_munif2008;
	set wealth_ranks_munif2007;
	rename muni2007 = muni2008;
run;

%macro m0bbbaaadddd(f_or_i);
%do I = 1986 %to 2007;
	%let J = %eval(&I+1);

proc sort nodupkey data = wealth_ranks_munif&I;
	by nybidnr;
run;

proc sort nodupkey data = wealth_ranks_munif&J;
	by nybidnr;
run;

data mobility&I;
	merge wealth_ranks_munif&I(keep = nybidnr p_wealth muni&I) wealth_ranks_munif&J(keep = nybidnr p_wealth muni&J rename = (p_wealth = p_wealth&J));
		by nybidnr;
run;

data mobility&I;
	set mobility&I;
		where muni&I>'0' and muni&I=muni&J;
run;

data mobility&I;
	set mobility&I;
		where p_wealth = 9;
	if p_wealth&J = 9 then mobility = 1; else mobility = 0;
run;

proc means data = mobility&I;
	var mobility;
run;

proc sort data = mobility&I;
	by muni&I;
run;

proc freq data = mobility&I;
	table mobility/out = wealth_mob&I outpercent;
		by muni&I;
run;

data wealth_mob&I;
	set wealth_mob&I;
		where mobility = 1;
run;

proc sort data = wealth_mob&I;
	by percent;
run;

data grund.wealth_mob&I;
	set wealth_mob&I;
		rename percent = topmobyear&I;
		keep muni&I percent;
run;

proc sort data = grund.wealth_mob&I;
	by muni&I;
run;

data grund.wealth_mob_mod&J;
	set wealth_mob&I;
		rename percent = topmob2year&J muni&I = muni&J;
		keep muni&I percent;
run;

proc sort data = grund.wealth_mob_mod&J;
	by muni&J;
run;

%end;
%mend;
%m0bbbaaadddd(f);


***************************************************************************************************************
*add ID variable for the wealth data at the municipality level (both for the regular and the immigrant samples)
***************************************************************************************************************;

data grund.wealth_mob_mod1986;
	set grund.wealth_mob_mod1987;
	rename muni1987=muni1986 topmob2year1987=topmob2year1986;
run; 

%macro m0bbbaaadf(f_or_i);
%do I = 1986 %to 2007;

proc sort data = grund.bostadsortf&I;
	by muni&I;
run;

proc sort data = grund.bostadsorti&I;
	by muni&I;
run;

data wealth_mob&I;
	set grund.wealth_mob_complete;
	rename muni = muni&I no_mob = no_mob&I topmob = topmob&I upmob_low = upmob_low&I upmob_high = upmob_high&I;
run;

data grund.avg_wealth_muni_indf&I;
	merge grund.avg_wealth_muni&I grund.avg_wealth_muninew&I grund.mean_wealth_muni&I grund.wealth_top10_muni&I grund.wealth_top20_muni&I grund.div_top10_muni&I grund.pval_top10_muni&I grund.ptax_top10_muni&I wealth_mob&I grund.wealth_mob&I grund.wealth_mob_mod&I grund.bostadsortf&I(in = in_bostad);
		by muni&I;
			if muni&I>'0' and in_bostad = 1;
	keep nybidnr avg_wealth_muni&I mean_wealth_muni&I p50_wealth_muni&I p75_wealth_muni&I p90_wealth_muni&I p95_wealth_muni&I share_top10_&I share_top1020_&I ptax_top10_&I pval_top10_&I div_top10_&I sdiv_top10_&I spval_top10_&I sptax_top10_&I no_mob&I topmob&I topmobyear&I upmob_low&I upmob_high&I topmob2year&I uni_top10_&I propo_top10_&I divea_top10_&I self_top10_&I;
		rename nybidnr = id;
run;

data grund.avg_wealth_muni_indi&I;
	merge grund.avg_wealth_muni&I grund.avg_wealth_muninew&I grund.mean_wealth_muni&I grund.wealth_top10_muni&I grund.wealth_top20_muni&I grund.div_top10_muni&I grund.pval_top10_muni&I grund.ptax_top10_muni&I wealth_mob&I grund.wealth_mob&I grund.wealth_mob_mod&I grund.bostadsorti&I(in = in_bostad);
		by muni&I;
			if muni&I>'0' and in_bostad = 1;
	keep nybidnr avg_wealth_muni&I mean_wealth_muni&I p50_wealth_muni&I p75_wealth_muni&I p90_wealth_muni&I p95_wealth_muni&I share_top10_&I share_top1020_&I ptax_top10_&I pval_top10_&I div_top10_&I sdiv_top10_&I spval_top10_&I sptax_top10_&I no_mob&I topmob&I topmobyear&I upmob_low&I upmob_high&I topmob2year&I uni_top10_&I propo_top10_&I divea_top10_&I self_top10_&I;
		rename nybidnr = id;
run;

proc sort data = grund.avg_wealth_muni_indi&I;
	by id;
run;

proc sort data = grund.avg_wealth_muni_indf&I;
	by id;
run;

%end;
%mend;
%m0bbbaaadf(f);

**merge all years;
data grund.avg_wealth_parishf;
	merge grund.avg_wealth_parish_indf1986-grund.avg_wealth_parish_indf1992;
		by id;
run;

data grund.avg_wealth_parishi;
	merge grund.avg_wealth_parish_indi1986-grund.avg_wealth_parish_indi1992;
		by id;
run;

data grund.avg_wealth_munif;
	merge grund.avg_wealth_muni_indf1986-grund.avg_wealth_muni_indf2007;
		by id;
run;

data grund.avg_wealth_munii;
	merge grund.avg_wealth_muni_indi1986-grund.avg_wealth_muni_indi2007;
		by id;
run;

data grund.taxwealthf;
	merge grund.taxwealthf1986-grund.taxwealthf1992;
		by id;
run;

data grund.taxwealthi;
	merge grund.taxwealthi1986-grund.taxwealthi1992;
		by id;
run;

*summary statistics;
proc means data = grund.avg_wealth_munif;
run;

proc means data = grund.avg_wealth_munii;
run;


*********************************************************************************************************************
**construct average income per electoral district, parish and municipality (do it only for the regular LINDA sample);
********************************************************************************************************************;

%macro m0bbbaaab(f_or_i);
%do I = 1987 %to 2007;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

data merged&f_or_i&I;
	merge grund.bostadsort&f_or_i&I(rename = (nybidnr = id)) grund.incomes&f_or_i&I grund.lindafamily&f_or_i&I(rename = (nybidnr = id)) grund.age&f_or_i&I(rename = (nybidnr = id));
		by id;
run;

*at the national level;
proc means data = merged&f_or_i&I noprint;
		var inc_earned;
	where burvkodf = 1 and 16<=age<=64 and inc_earned>0; *NOTE: we only consider those with positive income;
	output out = avg_inc&I median = avg_inc p75 = p75_inc;
run;

data avg_inc&I;
	set avg_inc&I;
		id_temp = 1;
	keep avg_inc p75_inc id_temp;
run;

*at the county level;
proc sort data = merged&f_or_i&I;
	by county&I;
run;

proc means data = merged&f_or_i&I noprint;
	class county&I;
		var inc_earned ;
	where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
	output out = avg_inc_county&I median = avg_inc_county p75 = p75_inc_county;
run;

*at the municipality level;
proc sort data = merged&f_or_i&I;
	by muni&I;
run;

proc means data = merged&f_or_i&I noprint;
	class muni&I;
		var inc_earned ;
	where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
	output out = avg_inc_muni&I median = avg_inc_muni p75 = p75_inc_muni;
run;

proc univariate data = merged&f_or_i&I noprint;
	var inc_earned; 
		class muni&I;
			where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
		output out=avg_inc_muninew&I pctlpre= inc_munip pctlpts = 10,20,50,80,90,95;
run;

*at the parish level;
proc sort data = merged&f_or_i&I;
	by parish&I;
run;

proc means data = merged&f_or_i&I noprint;
	class parish&I;
		var inc_earned ;
	where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
	output out = avg_inc_parish&I median = avg_inc_parish p75 = p75_inc_parish;
run;

proc univariate data = merged&f_or_i&I noprint;
	var inc_earned; 
		class parish&I;
			where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
		output out=avg_inc_parishnew&I pctlpre= inc_parishp pctlpts = 10,20,50,80,90,95;
run;

%if &I<2007 %then %do;

proc sort data = merged&f_or_i&I;
	by election&I;
run;

proc means data = merged&f_or_i&I noprint;
	class election&I;
		var inc_earned;
	where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
	output out = avg_inc_elect&I median = avg_inc_elect p75 = p75_inc_elect;
run;

proc univariate data = merged&f_or_i&I noprint;
	var inc_earned; 
		class election&I;
			where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
		output out=avg_inc_electnew&I pctlpre= inc_electp pctlpts = 10,20,50,80,90,95;
run;
		
%end;

%end;
%mend;
%m0bbbaaab(f);


********************************************************************************************************************************
**construct average DISPOSABLE INCOME per electoral district, parish and municipality (do it only for the regular LINDA sample);
*******************************************************************************************************************************;

%macro m0bbbaaab(f_or_i);
%do I = 1987 %to 2007;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

%if &I<1991 %then %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr din86);
		rename nybidnr = id din86 = disp_inc;
run;
%end;

%else %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr cdispl);
		rename nybidnr = id cdispl = disp_inc;
run;
%end;

proc sort data = income&f_or_i&I;
	by id;
run;

data merged_disp&f_or_i&I;
	merge grund.bostadsort&f_or_i&I(rename = (nybidnr = id)) income&f_or_i&I grund.lindafamily&f_or_i&I(rename = (nybidnr = id)) grund.age&f_or_i&I(rename = (nybidnr = id));
		by id;
run;

*at the national level;
proc means data = merged_disp&f_or_i&I noprint;
		var disp_inc;
	where burvkodf = 1 and 16<=age<=64 and disp_inc>0; *NOTE: we only consider those with positive income;
	output out = avg_disp_inc&I median = avg_disp_inc p75 = p75_disp_inc;
run;

data avg_disp_inc&I;
	set avg_disp_inc&I;
		id_temp = 1;
	keep avg_disp_inc p75_disp_inc id_temp;
run;

*at the county level;
proc sort data = merged_disp&f_or_i&I;
	by county&I;
run;

proc means data = merged_disp&f_or_i&I noprint;
	class county&I;
		var disp_inc ;
	where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
	output out = avg_disp_inc_county&I median = avg_disp_inc_county p75 = p75_disp_inc_county;
run;

*at the municipality level;
proc sort data = merged_disp&f_or_i&I;
	by muni&I;
run;

proc means data = merged_disp&f_or_i&I noprint;
	class muni&I;
		var disp_inc ;
	where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
	output out = avg_disp_inc_muni&I median = avg_disp_inc_muni p75 = p75_disp_inc_muni;
run;

proc univariate data = merged_disp&f_or_i&I noprint;
	var disp_inc; 
		class muni&I;
			where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
		output out=avg_disp_inc_muninew&I pctlpre= disp_inc_munip pctlpts = 10,20,50,80,90,95;
run;

*at the parish level;
proc sort data = merged_disp&f_or_i&I;
	by parish&I;
run;

proc means data = merged_disp&f_or_i&I noprint;
	class parish&I;
		var disp_inc ;
	where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
	output out = avg_disp_inc_parish&I median = avg_disp_inc_parish p75 = p75_disp_inc_parish;
run;

proc univariate data = merged_disp&f_or_i&I noprint;
	var disp_inc; 
		class parish&I;
			where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
		output out=avg_disp_inc_parishnew&I pctlpre= disp_inc_parishp pctlpts = 10,20,50,80,90,95;
run;

%if &I<2007 %then %do;

proc sort data = merged_disp&f_or_i&I;
	by election&I;
run;

proc means data = merged_disp&f_or_i&I noprint;
	class election&I;
		var disp_inc;
	where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
	output out = avg_disp_inc_elect&I median = avg_disp_inc_elect p75 = p75_disp_inc_elect;
run;

proc univariate data = merged_disp&f_or_i&I noprint;
	var disp_inc; 
		class election&I;
			where burvkodf = 1 and 16<=age<=64 and disp_inc>0;
		output out=avg_disp_inc_electnew&I pctlpre= disp_inc_electp pctlpts = 10,20,50,80,90,95;
run;
		
%end;

%end;
%mend;
%m0bbbaaab(f);


%macro m0bbbaaac(f_or_i);
%do I = 1987 %to 2007;

proc sort data = grund.bostadsort&f_or_i&I;
	by county&I;
run;

data avg_inc_county&f_or_i&I;
	merge avg_inc_county&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by county&I;
	if county&I>'0' and in_bostad = 1;
	keep nybidnr avg_inc_county p75_inc_county;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by muni&I;
run;

data avg_inc_muni&f_or_i&I;
	merge avg_inc_muni&I avg_inc_muninew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by muni&I;
	if muni&I>'0' and in_bostad = 1;
	keep nybidnr avg_inc_muni p75_inc_muni inc_munip10 inc_munip20 inc_munip50 inc_munip80 inc_munip90 inc_munip95;
	rename inc_munip50 = p50_inc_muni inc_munip10 = p10_inc_muni inc_munip20 = p20_inc_muni inc_munip80 = p80_inc_muni inc_munip90 = p90_inc_muni inc_munip95 = p95_inc_muni;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by parish&I;
run;

data avg_inc_parish&f_or_i&I;
	merge avg_inc_parish&I avg_inc_parishnew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by parish&I;
			if parish&I>'0' and in_bostad = 1;
	keep nybidnr _freq_ avg_inc_parish p75_inc_parish inc_parishp10 inc_parishp20 inc_parishp50 inc_parishp80 inc_parishp90 inc_parishp95;
	rename _freq_ = n_parish_inc inc_parishp50 = p50_inc_parish inc_parishp10 = p10_inc_parish inc_parishp20 = p20_inc_parish inc_parishp80 = p80_inc_parish inc_parishp90 = p90_inc_parish inc_parishp95 = p95_inc_parish;
run;

proc sort data = avg_inc_parish&f_or_i&I;
	by nybidnr;
run;

proc sort data = avg_inc_muni&f_or_i&I;
	by nybidnr;
run;

proc sort data = avg_inc_county&f_or_i&I;
	by nybidnr;
run;

data avg_inc_parishT&f_or_i&I;
	set avg_inc_parish&f_or_i&I;
		rename n_parish_inc = n_parish_inc&I avg_inc_parish = avg_inc_parish&I p10_inc_parish = p10_inc_parish&I p20_inc_parish = p20_inc_parish&I p50_inc_parish = p50_inc_parish&I p75_inc_parish = p75_inc_parish&I p95_inc_parish = p95_inc_parish&I p80_inc_parish = p80_inc_parish&I p90_inc_parish = p90_inc_parish&I nybidnr = id;
run;

data avg_inc_muniT&f_or_i&I;
	set avg_inc_muni&f_or_i&I;
		rename avg_inc_muni = avg_inc_muni&I p10_inc_muni = p10_inc_muni&I p20_inc_muni = p20_inc_muni&I p50_inc_muni = p50_inc_muni&I p75_inc_muni = p75_inc_muni&I p95_inc_muni = p95_inc_muni&I p80_inc_muni = p80_inc_muni&I p90_inc_muni = p90_inc_muni&I nybidnr = id;
run;

data grund.avg_incomes&f_or_i&I;
	merge avg_inc_parish&f_or_i&I avg_inc_muni&f_or_i&I avg_inc_county&f_or_i&I;
		by nybidnr;
		id_temp = 1;
run;

data grund.avg_incomes&f_or_i&I;
 	merge grund.avg_incomes&f_or_i&I(in = in_sample) avg_inc&I; 
 		by id_temp;
		if in_sample = 1;
 	drop id_temp;
run;

%if &I<2007 %then %do;
proc sort data = grund.bostadsort&f_or_i&I;
	by election&I;
run;

data avg_inc_elect&f_or_i&I;
	merge avg_inc_elect&I avg_inc_electnew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by election&I;
			if election&I>'0' and in_bostad = 1;
	keep nybidnr avg_inc_elect p75_inc_elect inc_electp90 inc_electp95;
	rename inc_electp90 = p90_inc_elect inc_electp95 = p95_inc_elect;
run;

proc sort data = avg_inc_elect&f_or_i&I;
	by nybidnr;
run;

data avg_inc_electT&f_or_i&I;
	set avg_inc_elect&f_or_i&I;
		rename avg_inc_elect = avg_inc_elect&I p75_inc_elect = p75_inc_elect&I p90_inc_elect = p90_inc_elect&I p95_inc_elect = p95_inc_elect&I nybidnr = id;
run;

data grund.avg_incomes&f_or_i&I;
	merge grund.avg_incomes&f_or_i&I avg_inc_elect&f_or_i&I;
		by nybidnr;
run;
%end;


proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;


%end;
%mend;
%m0bbbaaac(f);
%m0bbbaaac(i);



%macro m0bbbaaac(f_or_i);
%do I = 1987 %to 2007;

proc sort data = grund.bostadsort&f_or_i&I;
	by county&I;
run;

data avg_disp_inc_county&f_or_i&I;
	merge avg_disp_inc_county&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by county&I;
	if county&I>'0' and in_bostad = 1;
	keep nybidnr avg_disp_inc_county p75_disp_inc_county;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by muni&I;
run;

data avg_disp_inc_muni&f_or_i&I;
	merge avg_disp_inc_muni&I avg_disp_inc_muninew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by muni&I;
	if muni&I>'0' and in_bostad = 1;
	keep nybidnr avg_disp_inc_muni p75_disp_inc_muni disp_inc_munip10 disp_inc_munip20 disp_inc_munip50 disp_inc_munip80 disp_inc_munip90 disp_inc_munip95;
	rename disp_inc_munip50 = p50_disp_inc_muni disp_inc_munip10 = p10_disp_inc_muni disp_inc_munip20 = p20_disp_inc_muni disp_inc_munip80 = p80_disp_inc_muni disp_inc_munip90 = p90_disp_inc_muni disp_inc_munip95 = p95_disp_inc_muni;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by parish&I;
run;

data avg_disp_inc_parish&f_or_i&I;
	merge avg_disp_inc_parish&I avg_disp_inc_parishnew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by parish&I;
			if parish&I>'0' and in_bostad = 1;
	keep nybidnr _freq_ avg_disp_inc_parish p75_disp_inc_parish disp_inc_parishp10 disp_inc_parishp20 disp_inc_parishp50 disp_inc_parishp80 disp_inc_parishp90 disp_inc_parishp95;
	rename _freq_ = n_parish_disp_inc disp_inc_parishp50 = p50_disp_inc_parish disp_inc_parishp10 = p10_disp_inc_parish disp_inc_parishp20 = p20_disp_inc_parish disp_inc_parishp80 = p80_disp_inc_parish disp_inc_parishp90 = p90_disp_inc_parish disp_inc_parishp95 = p95_disp_inc_parish;
run;

proc sort data = avg_disp_inc_parish&f_or_i&I;
	by nybidnr;
run;

proc sort data = avg_disp_inc_muni&f_or_i&I;
	by nybidnr;
run;

proc sort data = avg_disp_inc_county&f_or_i&I;
	by nybidnr;
run;

data avg_disp_inc_parishT&f_or_i&I;
	set avg_disp_inc_parish&f_or_i&I;
		rename n_parish_disp_inc = n_parish_disp_inc&I avg_disp_inc_parish = avg_disp_inc_parish&I p10_disp_inc_parish = p10_disp_inc_parish&I p20_disp_inc_parish = p20_disp_inc_parish&I p50_disp_inc_parish = p50_disp_inc_parish&I p75_disp_inc_parish = p75_disp_inc_parish&I p95_disp_inc_parish = p95_disp_inc_parish&I p80_disp_inc_parish = p80_disp_inc_parish&I p90_disp_inc_parish = p90_disp_inc_parish&I nybidnr = id;
run;

data avg_disp_inc_muniT&f_or_i&I;
	set avg_disp_inc_muni&f_or_i&I;
		rename avg_disp_inc_muni = avg_disp_inc_muni&I p10_disp_inc_muni = p10_disp_inc_muni&I p20_disp_inc_muni = p20_disp_inc_muni&I p50_disp_inc_muni = p50_disp_inc_muni&I p75_disp_inc_muni = p75_disp_inc_muni&I p95_disp_inc_muni = p95_disp_inc_muni&I p80_disp_inc_muni = p80_disp_inc_muni&I p90_disp_inc_muni = p90_disp_inc_muni&I nybidnr = id;
run;

data grund.avg_disp_incomes&f_or_i&I;
	merge avg_disp_inc_parish&f_or_i&I avg_disp_inc_muni&f_or_i&I avg_disp_inc_county&f_or_i&I;
		by nybidnr;
		id_temp = 1;
run;

data grund.avg_disp_incomes&f_or_i&I;
 	merge grund.avg_disp_incomes&f_or_i&I(in = in_sample) avg_disp_inc&I; 
 		by id_temp;
		if in_sample = 1;
 	drop id_temp;
run;

%if &I<2007 %then %do;
proc sort data = grund.bostadsort&f_or_i&I;
	by election&I;
run;

data avg_disp_inc_elect&f_or_i&I;
	merge avg_disp_inc_elect&I avg_disp_inc_electnew&I grund.bostadsort&f_or_i&I(in = in_bostad);
		by election&I;
			if election&I>'0' and in_bostad = 1;
	keep nybidnr avg_disp_inc_elect p75_disp_inc_elect disp_inc_electp90 disp_inc_electp95;
	rename disp_inc_electp90 = p90_disp_inc_elect disp_inc_electp95 = p95_disp_inc_elect;
run;

proc sort data = avg_disp_inc_elect&f_or_i&I;
	by nybidnr;
run;

data avg_disp_inc_electT&f_or_i&I;
	set avg_disp_inc_elect&f_or_i&I;
		rename avg_disp_inc_elect = avg_disp_inc_elect&I p75_disp_inc_elect = p75_disp_inc_elect&I p90_disp_inc_elect = p90_disp_inc_elect&I p95_disp_inc_elect = p95_disp_inc_elect&I nybidnr = id;
run;

data grund.avg_disp_incomes&f_or_i&I;
	merge grund.avg_disp_incomes&f_or_i&I avg_disp_inc_elect&f_or_i&I;
		by nybidnr;
run;
%end;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbaaac(f);
%m0bbbaaac(i);


data grund.avg_inc_electf;
	merge avg_inc_electTf1987-avg_inc_electTf1993;
		by id;
run;

data grund.avg_inc_electi;
	merge avg_inc_electTi1987-avg_inc_electTi1993;
		by id;
run;

data grund.avg_inc_parishf;
	merge avg_inc_parishTf1987-avg_inc_parishTf2007;
		by id;
run;

data grund.avg_inc_parishi;
	merge avg_inc_parishTi1987-avg_inc_parishTi2007;
		by id;
run;

data grund.avg_inc_munif;
	merge avg_inc_muniTf1987-avg_inc_muniTf1993;
		by id;
run;

data grund.avg_inc_munii;
	merge avg_inc_muniTi1987-avg_inc_muniTi1993;
		by id;
run;


data grund.avg_disp_inc_parishf;
	merge avg_disp_inc_parishTf1987-avg_disp_inc_parishTf2007;
		by id;
run;

data grund.avg_disp_inc_parishi;
	merge avg_disp_inc_parishTi1987-avg_disp_inc_parishTi2007;
		by id;
run;


*Average disposable income and unemployment rate per municipality*;
%macro m0bbbaw(f_or_i);
%do I = 1986 %to 2007;

%if &I<1991 %then %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr din86);
		rename nybidnr = id din86 = disp_inc;
run;
%end;

%else %do;
data income&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr cdispl);
		rename nybidnr = id cdispl = disp_inc;
run;
%end;

proc sort data = income&f_or_i&I;
	by id;
run;

proc sort data = grund.bostadsort&f_or_i&I;
	by nybidnr;
run;

data merged&f_or_i&I;
	merge grund.unemployment&f_or_i&I grund.bostadsort&f_or_i&I(rename = (nybidnr = id)) income&f_or_i&I grund.lindafamily&f_or_i&I(rename = (nybidnr = id)) grund.age&f_or_i&I(rename = (nybidnr = id));
		by id;
run;

proc sort data = merged&f_or_i&I;
	by muni&I;
run;

proc means data = merged&f_or_i&I;
	class muni&I;
		var disp_inc;
			where burvkodf = 1 and 16<=age<=64; *NOTE: sampled individuals between 16 and 64 y/o;
		output out = avg_inc_muni&I mean = ;
run;

data grund.avgincbymuni&I;
	set avg_inc_muni&I;
			where muni&I>'0';
	rename disp_inc = avg_inc_muni&I;
	keep muni&I disp_inc;
run;

proc freq data = merged&f_or_i&I;
	tables muni&I / noprint out = unemployedbymuni&I;
	where burvkodf = 1 and 16<=age<=64;
run;


proc freq data = merged&f_or_i&I;
	tables muni&I / noprint out = unemployedbymuni2&I;
	where unemploybenefits&I>0 and burvkodf = 1 and 16<=age<=64; 
run;

data grund.unemployedbymuni&I;
	merge unemployedbymuni2&I(rename = (COUNT = unemployed)) unemployedbymuni&I;
		by muni&I;
	where muni&I>'0';
	if unemployed = . then unemployed = 0;
		unemployed_rate&I = unemployed/count;
	keep muni&I unemployed_rate&I;
run;

%end;
%mend;
%m0bbbaw(f); 


data grund.unemployedbymuni1985;
	set grund.unemployedbymuni1987;
		unemployed_rate1985 = .;
	drop unemployed_rate1987;
	rename muni1987 = muni1985;
run;

data grund.avgincbymuni1985;
	set grund.avgincbymuni1987;
		avg_inc_muni1985 = .;
	drop avg_inc_muni1987;
	rename muni1987 = muni1985;
run;

data grund.avgincbymuni1986;
	set grund.avgincbymuni1987;
		avg_inc_muni1986 = .;
	drop avg_inc_muni1987;
	rename muni1987 = muni1986;
run;


**************************************************;
**Estimate population density per municipality	**;
**************************************************;

%macro m0aeee;
%do I = 1985 %to 1991;
data grund.popdensity&I;
	set original.densityperkommun;
		keep municapility v1991;
		rename municapility = muni&I v1991 = pop_density_muni&I;
run;
%end;

%do I = 1992 %to 1993;
data grund.popdensity&I;
	set original.densityperkommun;
		keep municapility v&I;
		rename municapility = muni&I v&I = pop_density_muni&I;
run;
%end;

%mend;
%m0aeee;


**************************************************;
**Population per parish as of 2013				**;
**************************************************;

data pop_parish;
	set original.befolkningperforsamling2013;
		parishup = parish+1000000;
				regionup2 = put(parishup,7.);
				parishnew = substr(regionup2,2,6);
	keep parishnew totalt;
run;

data grund.pop2003_parish;
	set pop_parish;
	rename totalt = pop2003_parish parishnew = parish;
run;


/*********************************************/
/********* ASSETS and LIABILITIES ************/
/*********************************************/

*get information about interest payments on debt;
%macro m0bbbc(f_or_i);
%do I = 1985 %to 2007;

%if &I<1991 %then %do;
data grund.debt&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr);
	debtholder = .; 
run;
%end;

%if &I>1990 and &I<1993 %then %do;
data grund.debt&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr KAPSRTA);
	if KAPSRTA>0 then debtholder = 1; else debtholder = 0; 
run;
%end;

%if &I=1993 %then %do;
data grund.debt&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr KASKU);
	if KASKU>0 then debtholder = 1; else debtholder = 0; 
run;
%end;

%if &I>1993 %then %do;
data grund.debt&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr KAKURTA);
	if KAKURTA>0 then debtholder = 1; else debtholder = 0; 
run;
%end;

proc sort data = grund.debt&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbc(f);
%m0bbbc(i);


*Average loan-to-income at the electoral district level (Note that debt is only available from 1991);
%macro m0bbbcd(f_or_i);
%do I = 1987 %to 1992;

%if &I<1991 %then %do;
data skuld&f_or_i&I;
	set original.linda&f_or_i.1991 (keep = nybidnr FORMSKU KAPSRTA);
		debt&I = FORMSKU*100;
		interest&I = KAPSRTA*100;
		drop FORMSKU KAPSRTA;
run;

proc sort data = skuld&f_or_i&I;
	by nybidnr;
run;

data merged&f_or_i&I;
	merge skuld&f_or_i&I grund.bostadsort&f_or_i&I grund.incomes&f_or_i.1991(rename = (id = nybidnr)) grund.lindafamily&f_or_i.1991(in = in_linda);
		by nybidnr;
		if in_linda = 1;
run;
%end;

%if &I>1990 and &I<1993 %then %do;
data skuld&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr FORMSKU KAPSRTA);
		debt&I = FORMSKU*100;
		interest&I = KAPSRTA*100;
		drop FORMSKU KAPSRTA;
run;

proc sort data = skuld&f_or_i&I;
	by nybidnr;
run;

data merged&f_or_i&I;
	merge skuld&f_or_i&I grund.bostadsort&f_or_i&I grund.incomes&f_or_i&I(rename = (id = nybidnr)) grund.lindafamily&f_or_i&I(in = in_linda);
		by nybidnr;
		if in_linda = 1;
run;
%end;

%if &I=1993 %then %do;
data skuld&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr KASKU FSKUKAP);
		debt&I = FSKUKAP*100;
		interest&I = KASKU*100;
		drop FSKUKAP KASKU;
run;
%end;

%if &I>1993 %then %do;
data skuld&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr KASKU KAKURTA FSKULD);	
		rename FSKULD = debt&I KASKU = interest&I KAKURTA = interestN&I;
run;
%end;

%if &I>1998 %then %do;
data skuldN&f_or_i&I;
	set original.lform&f_or_i&I (keep = nybidnr FKURTA);
	rename FKURTA = debt_form&I;
run;

proc sort data = skuldN&f_or_i&I;
	by nybidnr;
run;
%end;

%if &I<1993 %then %do;
proc sort data = merged&f_or_i&I;
	by nybidnrh nybidnr descending election&I;
run;

data skuldFAM&f_or_i&I (keep = nybidnrh inchh_earned debt_hh election&I);				
   	set merged&f_or_i&I;
   		by nybidnrh;
   			if first.nybidnrh then do;
        		debt_hh = 0;
	   		end;
   			debt_hh + debt&I;
			if last.nybidnrh then output;
run;

data skuldFAM&f_or_i&I;
	set skuldFAM&f_or_i&I;
		if debt_hh>=0 and inchh_earned>0 then dti = debt_hh/(inchh_earned*100);
		if debt_hh>=0 and inchh_earned=0 then dti = 0;
	rename election&I = election;
run;

proc sort data = skuldFAM&f_or_i&I;
	by election;
run;

proc means data = skuldFAM&f_or_i&I noprint;
	class election;
		var dti;
	where election>'0';
	output out = avg_dti_elect&I median = avg_dti_elect&I p75 = p75_dti_elect&I;
run;

data avg_dti_elect&I;
	set avg_dti_elect&I;
		keep election avg_dti_elect&I p75_dti_elect&I;
run;
%end;

%end;
%mend;
%m0bbbcd(f);

data grund.avg_dti_elect1987;
	merge avg_dti_elect1987(in = in_master) avg_dti_elect1988-avg_dti_elect1991;
		by election;
			if in_master = 1;
	avg_dti_elect1987T = avg_dti_elect1987;
	avg_dti_elect1987 = avg_dti_elect1991;
	if avg_dti_elect1987 =. then avg_dti_elect1987 = avg_dti_elect1990;
	if avg_dti_elect1987 =. then avg_dti_elect1987 = avg_dti_elect1989;
	if avg_dti_elect1987 =. then avg_dti_elect1987 = avg_dti_elect1988;
	if avg_dti_elect1987 =. then avg_dti_elect1987 = avg_dti_elect1987T;
		keep election avg_dti_elect1987;
run;

data grund.avg_dti_elect1988;
	merge avg_dti_elect1988(in = in_master) avg_dti_elect1989-avg_dti_elect1991;
		by election;
			if in_master = 1;
	avg_dti_elect1988T = avg_dti_elect1988;
	avg_dti_elect1988 = avg_dti_elect1991;
	if avg_dti_elect1988 =. then avg_dti_elect1988 = avg_dti_elect1990;
	if avg_dti_elect1988 =. then avg_dti_elect1988 = avg_dti_elect1989;
	if avg_dti_elect1988 =. then avg_dti_elect1988 = avg_dti_elect1988T;
		keep election avg_dti_elect1988;
run;

data grund.avg_dti_elect1989;
	merge avg_dti_elect1989(in = in_master) avg_dti_elect1990-avg_dti_elect1991;
		by election;
			if in_master = 1;
	avg_dti_elect1989T = avg_dti_elect1989;
	avg_dti_elect1989 = avg_dti_elect1991;
	if avg_dti_elect1989 =. then avg_dti_elect1989 = avg_dti_elect1990;
	if avg_dti_elect1989 =. then avg_dti_elect1989 = avg_dti_elect1989T;
		keep election avg_dti_elect1989;	
run;

data grund.avg_dti_elect1990;
	merge avg_dti_elect1990(in = in_master) avg_dti_elect1991;
		by election;
			if in_master = 1;
	avg_dti_elect1990T = avg_dti_elect1990;
	avg_dti_elect1990 = avg_dti_elect1991;
	if avg_dti_elect1990 =. then avg_dti_elect1990 = avg_dti_elect1990T;
		keep election avg_dti_elect1990;	
run;

data grund.avg_dti_elect1991;
	set avg_dti_elect1991;
run; 

data grund.avg_dti_elect1992;
	set avg_dti_elect1992;
run; 

data allyears;
	merge grund.avg_dti_elect1987-grund.avg_dti_elect1992;
	by election;
run;


*get information about bank accounts;
%macro m0bbbcc(f_or_i);
%do I = 1988 %to 2007;

%if &I<1993 %then %do;
data grund.bankaccount&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr BFORDR);
	if BFORDR>0 then bankholder = 1; else bankholder = 0; 
run;
%end;

%if &I>=1993 %then %do;
data grund.bankaccount&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr FKUBANK);
	if FKUBANK>0 then bankholder = 1; else bankholder = 0; 
run;
%end;

proc sort data = grund.bankaccount&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;
%m0bbbcc(f);
%m0bbbcc(i);


*Get information about retirement savings (only available from 1994);
%macro m0bbbc(f_or_i);
%do I = 1994 %to 2007;

data grund.pensionsaving&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr akupens);
	if akupens>0 then retsaver = 1; else retsaver = 0;
run;

proc sort data = grund.pensionsaving&f_or_i&I;
	by nybidnr;
run;

data firstyearret&f_or_i&I;
	set grund.pensionsaving&f_or_i&I;
		where retsaver = 1;
			rename retsaver = retsaver&I;
run;

%end;
%mend;
%m0bbbc(f);
%m0bbbc(i);


data firstyearret;
	merge firstyearretf1994-firstyearretf2007 firstyearreti1994-firstyearreti2007;
 		by nybidnr;
run;

data firstyearret2;
set firstyearret;
	if retsaver2007 = 1 then first_retsaver = 2007;
	if retsaver2006 = 1 then first_retsaver = 2006;
	if retsaver2005 = 1 then first_retsaver = 2005;
	if retsaver2004 = 1 then first_retsaver = 2004;
	if retsaver2003 = 1 then first_retsaver = 2003;
	if retsaver2002 = 1 then first_retsaver = 2002;
	if retsaver2001 = 1 then first_retsaver = 2001;
	if retsaver2000 = 1 then first_retsaver = 2000;
	if retsaver1999 = 1 then first_retsaver = 1999;
	if retsaver1998 = 1 then first_retsaver = 1998;
	if retsaver1997 = 1 then first_retsaver = 1997;
	if retsaver1996 = 1 then first_retsaver = 1996;
	if retsaver1995 = 1 then first_retsaver = 1995;
	if retsaver1994 = 1 then first_retsaver = 1994;
run;

%macro m0bbbe(f_or_i);
%do I = 1994 %to 2007;

data firstyearretFAM&f_or_i&I;
	merge grund.lindaFAMILY&f_or_i&I(in = in_linda) firstyearret2;
		by nybidnr;
		if in_linda = 1 and first_retsaver>0;
	keep nybidnrh first_retsaver;
run;

proc sort data = firstyearretFAM&f_or_i&I;
	by nybidnrh first_retsaver;
run;

proc sort nodupkey data = firstyearretFAM&f_or_i&I out = firstyearretFAM2&f_or_i&I;
	by nybidnrh;
run;

data grund.pensionsavingFAM&f_or_i&I;
	merge grund.lindaFAMILY&f_or_i&I grund.pensionsaving&f_or_i&I;
	if retsaver = 1; *keep only individuals who save for retirement;
		by nybidnr;
	keep nybidnrh retsaver;
run;

proc sort nodupkey data = grund.pensionsavingFAM&f_or_i&I;
	by nybidnrh;
run;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnrh;
run;

data grund.pensionsavingFAM&f_or_i&I;
	merge grund.pensionsavingFAM&f_or_i&I grund.lindaFAMILY&f_or_i&I(in = in_linda) firstyearretFAM2&f_or_i&I;
		by nybidnrh;
		if in_linda = 1;
			if retsaver = 1 then save_ret_hh = 1; else save_ret_hh = 0;
			if first_retsaver>0 then save_ret_year = first_retsaver; else save_ret_year = 0;
		drop nybidnrh retsaver first_retsaver;
	rename nybidnr = id;
run;

proc sort data = grund.pensionsavingFAM&f_or_i&I;
	by id;
run;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnr;
run;

%end;
%mend;

%m0bbbe(f);
%m0bbbe(i);


************************************************************************;
*Get information about tax deductions for commuting to work (reseavdrag);
************************************************************************;

%macro m0bbbc(f_or_i);
%do I = 1999 %to 2007;

data grund.commuting&f_or_i&I;
	set original.linda&f_or_i&I (keep = nybidnr tarese);
		if tarese>0 then commuter&I = 1; else commuter&I = 0;
			rename nybidnr = id;
	keep nybidnr commuter&I; 
run;

proc sort data = grund.commuting&f_or_i&I;
	by id;
run;

proc means data = grund.commuting&f_or_i&I;
	var commuter&I;
run;

*Output to excel (.csv);
proc export data = grund.commuting&f_or_i&I
		 outfile = "\\micro.intra\projekt\P0459$\P0459_gem\SAS DATA\Thomas\Projekt2\RFS\bal\commuting_data&f_or_i&I..csv" dbms=csv replace;
run;

%end;
%mend;
%m0bbbc(f);
%m0bbbc(i);


*********************************************************************;
*Get information about entrepreneural income and define entrepreneurs;
*********************************************************************;

%macro m0bbbaabc(f_or_i);

data entreprenourtemp&f_or_i.1991;			
	set original.linda&f_or_i.1991 (keep = nybidnr tjfoab); *TJFOAB = inkomst fr�n f�mansf�retag som redovisas som inkomst av tj�nst;
run;

proc sort data = entreprenourtemp&f_or_i.1991;
	by nybidnr;
run;

%do I = 1985 %to 2007;

	%if &I<1991 %then %do;
		%if &I<1987 %then %do;
		data grund.entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr insror foab sni insjor marb);
   				rename insror = enterpr_inc foab = closely_held_firm; *INSROR = inkomst av r�relse; *FOAB = mark. f�r f�mansdel�gare;
					closely_held_inc =.;
					inc_type = marb*1;						
				if (substr(sni,1,1) = '1' or insjor>0) then farmer = 1; else farmer = 0; *define farmers; *INSJOR = inkomst av jordbruksfastighet (brukad och ej brukad);	
				if insror>0 and farmer = 0 then selfemployed = 1; else selfemployed = 0; *exclude farmers from self-employed; 
				if farmer = 1 then insror = 0; *if farmer set income = 0;
				if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
				enterpr_inc_mod = insror;
					drop marb sni insjor;
		run;
		%end;

		%if &I>1986 %then %do;
		data entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr insror foab kusni insjor marb);   				
		run;

		proc sort data = entreprenour&f_or_i&I;
			by nybidnr;
		run;

		data grund.entreprenour&f_or_i&I;
			merge entreprenour&f_or_i&I(in = in_entr) entreprenourtemp&f_or_i.1991; *NOTE: TJFOAB is from 1991 TJFOAB = inkomst fr�n f�mansf�retag som redovisas som inkomst av tj�nst;
				by nybidnr;
			if in_entr = 1;
				rename tjfoab = closely_held_inc insror = enterpr_inc foab = closely_held_firm;
					inc_type = marb*1;						
				if (substr(kusni,1,1) = '1' or insjor>0) then farmer = 1; else farmer = 0;	
				if (tjfoab>0 or insror>0) and farmer = 0 then selfemployed = 1; else selfemployed = 0; *selfemployed refer to enterpr_inc_mod;
				if farmer = 1 then insror = 0; *if farmer then set income = 0;
				if farmer = 1 then tjfoab = 0; *if farmer then set income = 0;
				if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
				enterpr_inc_mod = insror + tjfoab;
					drop marb kusni;
		run;
		%end;
	%end;

	%if &I = 1991 %then %do;
		data entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr innrv tjfoab foab kusni marb);
		run;

		proc sort data = entreprenour&f_or_i&I;
			by nybidnr;
		run;

		data grund.entreprenour&f_or_i&I;
			merge entreprenour&f_or_i&I(in = in_entr) grund.entreprenour&f_or_i.1990(keep = nybidnr insjor); *NOTE: INSJOR �r fr�n 1990 INSJOR = inkomst av jordbruksfastighet (brukad och ej brukad);
				by nybidnr;
			if in_entr = 1;
		   		rename innrv = enterpr_inc tjfoab = closely_held_inc foab = closely_held_firm; *INNRV = inkomst av n�ringsverksamhet;
				inc_type = marb*1;				
				if (substr(kusni,1,1) = '1' or insjor>0) then farmer = 1; else farmer = 0;	
				if (tjfoab>0 or innrv>0) and farmer = 0 then selfemployed = 1; else selfemployed = 0;
				if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
				if farmer = 1 then innrv = 0;	
				if farmer = 1 then tjfoab = 0;	
				enterpr_inc_mod = innrv + tjfoab;
		run;
		
		data grund.entreprenour&f_or_i&I;
			set grund.entreprenour&f_or_i&I;
				drop marb kusni insjor;
		run;

	%end;

	%if &I = 1992 %then %do;
		data entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr innrv tjfoab kusni marb);
		run;

		proc sort data = entreprenour&f_or_i&I;
			by nybidnr;
		run;
		
		data grund.entreprenour&f_or_i&I;
			merge entreprenour&f_or_i&I(in = in_entr) grund.entreprenour&f_or_i.1991(keep = nybidnr closely_held_firm) grund.entreprenour&f_or_i.1990(keep = nybidnr insjor);
			*NOTE: INSJOR �r fr�n 1990 INSJOR = inkomst av jordbruksfastighet (brukad och ej brukad);
				by nybidnr;
			if in_entr = 1;
		   		rename innrv = enterpr_inc tjfoab = closely_held_inc; *INNRV = inkomst av n�ringsverksamhet;
				inc_type = marb*1;
				if (tjfoab>0 or closely_held_firm = 1) then closely_held_firm2 = '1'; else closely_held_firm2 = '0';
				closely_held_firm = closely_held_firm2;
				if (substr(kusni,1,1) = '1' or insjor>0) then farmer = 1; else farmer = 0;	
				if (tjfoab>0 or innrv>0) and farmer = 0 then selfemployed = 1; else selfemployed = 0;
				if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
				if farmer = 1 then innrv = 0;	
				if farmer = 1 then tjfoab = 0;	
				enterpr_inc_mod = innrv + tjfoab;
		run;

		data grund.entreprenour&f_or_i&I;
			set grund.entreprenour&f_or_i&I;
				drop marb kusni insjor closely_held_firm2;
		run;
	%end;

	%if &I>1992 %then %do;

		%if &I<1999 %then %do;
		data grund.entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr nrv tfoab barbink BKUNGR);
   		rename nrv = enterpr_inc tfoab = closely_held_inc; *NRV = inkomst av n�ringsverksamhet;
			inc_type = barbink*1;
				if '01000'<bkungr<'10000' then farmer = 1; else farmer = 0;
				if tfoab>0 then closely_held_firm = '1'; else closely_held_firm = '0'; 
				if (tfoab>0 or nrv>0) and farmer = 0 then selfemployed = 1; else selfemployed = 0;
				if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
				if farmer = 1 then nrv = 0;
				if farmer = 1 then tfoab = 0;
			enterpr_inc_mod = nrv + tfoab;
				drop barbink BKUNGR;
		run;
		%end;

		%if &I>1998 %then %do;
		data grund.entreprenour&f_or_i&I;			
  			set original.linda&f_or_i&I (keep = nybidnr nrv tfoab bfoab barbink BKUNGR bngr bngre);
   		rename nrv = enterpr_inc tfoab = closely_held_inc;
			inc_type = barbink*1;
					if '01000'<bkungr<'10000' then farmer = 1; else farmer = 0;
					if '01000'<bngr<'10000' then farmer = 1;
					if '01000'<bngre<'10000' then farmer = 1;
					if bfoab = 1 then closely_held_firm = '1'; else closely_held_firm = '0';
					if (tfoab>0 or nrv>0) and farmer = 0 then selfemployed = 1; else selfemployed = 0;
					if (inc_type = 3 or inc_type = 6) and farmer = 0 then selfemployed_akt = 1; else selfemployed_akt = 0;
					if farmer = 1 then nrv = 0;
					if farmer = 1 then tfoab = 0;
			enterpr_inc_mod = nrv + tfoab;
			sni_entr_temp = bngre;
			sni_entr_alt_temp = bngr;
				drop barbink BKUNGR bngr bngre;
		run;

		proc sort data = grund.entreprenour&f_or_i&I;
			by nybidnr;
		run;

		data enthh&f_or_i&I;
			merge grund.entreprenour&f_or_i&I(keep = nybidnr sni_entr_temp sni_entr_alt_temp enterpr_inc) grund.lindafamily&f_or_i&I;
				by nybidnr;
				if enterpr_inc>0 and sni_entr_alt_temp>'0';
				rename sni_entr_temp = sni_entr_hh sni_entr_alt_temp = sni_entr_alt_hh; 
		run;

		proc sort data = enthh&f_or_i&I;
			by nybidnrh descending enterpr_inc;
		run;

		proc sort nodupkey data = enthh&f_or_i&I out = entr_snihh&f_or_i&I(keep = nybidnrh sni_entr_hh sni_entr_alt_hh);
			by nybidnrh;
		run;

		proc sort data = grund.lindafamily&f_or_i&I out = lindafamily&f_or_i&I;
			by nybidnrh;
		run;

		data grund.entr_snihh&f_or_i&I;
			merge entr_snihh&f_or_i&I lindafamily&f_or_i&I;
				by nybidnrh;
			rename nybidnr = id;
			keep nybidnr sni_entr_hh sni_entr_alt_hh;
		run;

		proc sort data = grund.entr_snihh&f_or_i&I;
			by id;
		run;

		%end;

	%end;

proc means data = grund.entreprenour&f_or_i&I noprint;
		var enterpr_inc;
	where enterpr_inc>0;
	output out = avg_enterpr_inc&f_or_i&I median = avg_enterpr_inc p75 = p75_enterpr_inc;
run;

data avg_enterpr_inc&f_or_i&I;
	set avg_enterpr_incf&I; *note: we only use the average income from the regular LINDA sample;
		temp_id = 1; 
	keep temp_id avg_enterpr_inc p75_enterpr_inc;
run;

data grund.entreprenour&f_or_i&I;
	set grund.entreprenour&f_or_i&I;
		temp_id = 1;
run;

data grund.entreprenour&f_or_i&I;
	merge grund.entreprenour&f_or_i&I(in = in_sample) avg_enterpr_inc&f_or_i&I; 
		by temp_id;
		if in_sample;
run;


proc sort data = grund.entreprenour&f_or_i&I;
	by nybidnr;
run;

%if &I>1986 %then %do;
	data grund.entreprenour&f_or_i&I;
		merge grund.entreprenour&f_or_i&I(in = in_entr) grund.avg_incomes&f_or_i&I grund.incomes&f_or_i&I(rename = (id = nybidnr));
			by nybidnr;
		if in_entr = 1;
			if selfemployed>0 then selfemp_inc = enterpr_inc_mod;
			if selfemployed>0 then selfemp_inc_s = selfemp_inc-avg_inc_parish; *NOTE: Average inc is where burvkodf = 1 and 16<=age<=64 and inc_earned>0;
			if selfemployed>0 then selfemp_inc_q3 = selfemp_inc-p75_inc_parish; *NOTE: We use average income in the parish;

			if enterpr_inc>0 then enterpr_inc_s = enterpr_inc-avg_inc_parish;
			if enterpr_inc>0 then enterpr_inc_q3 = enterpr_inc-p75_inc_parish;

			if enterpr_inc>0 then enterpr_inc_sall = enterpr_inc-avg_inc;
			if enterpr_inc>0 then enterpr_inc_scou = enterpr_inc-avg_inc_county;
			if enterpr_inc>0 then enterpr_inc_sent = enterpr_inc-avg_enterpr_inc;
			if enterpr_inc>0 then enterpr_inc_q3ent = enterpr_inc-p75_enterpr_inc;

			if closely_held_inc>0 then closely_held_inc_s = closely_held_inc-avg_inc_parish;
			if closely_held_inc>0 then closely_held_inc_q3 = closely_held_inc-p75_inc_parish;
		drop inchh_earned inchh_broad inchh_assessed inchh_taxable inc_earned inc_broad inc_assessed inc_taxable;
	run;
%end;

data entreprenourFAM&f_or_i&I;
	merge grund.lindaFAMILY&f_or_i&I grund.entreprenour&f_or_i&I;
		by nybidnr;
	keep nybidnr nybidnrh enterpr_inc enterpr_inc_mod closely_held_inc farmer selfemployed closely_held_firm;
run;

proc sort data = entreprenourFAM&f_or_i&I;
	by nybidnrh nybidnr;
run;

data entreprenourFAMinc&f_or_i&I (keep = nybidnrh enterpr_inc_hh enterpr_inc_mod_hh closely_held_inc_hh farmer_hh selfemployed_hh closely_held_firm_hh);				
   set entreprenourFAM&f_or_i&I;
   by nybidnrh;
   if first.nybidnrh then do;
         enterpr_inc_hh=0;
		 closely_held_inc_hh=0;
		 farmer_hh=0;
		 selfemployed_hh=0;
		 closely_held_firm_hh=0;
		 enterpr_inc_mod_hh=0;
	   end;
   		enterpr_inc_hh + enterpr_inc;
		closely_held_inc_hh + closely_held_inc;
		farmer_hh + farmer;
		selfemployed_hh + selfemployed;
		closely_held_firm_hh + closely_held_firm;
		enterpr_inc_mod_hh + enterpr_inc_mod;
	if last.nybidnrh then output;
run;

proc sort data = entreprenourFAM&f_or_i&I;
	by nybidnrh;
run;

data grund.entreprenourFAM&f_or_i&I;
	merge entreprenourFAMinc&f_or_i&I entreprenourFAM&f_or_i&I;
		by nybidnrh;
	keep nybidnr enterpr_inc_hh closely_held_inc_hh farmer_hh selfemployed_hh closely_held_firm_hh enterpr_inc_mod_hh;
	rename nybidnr = id;
run;

proc sort data = grund.entreprenour&f_or_i&I;
	by nybidnr;
run;

proc sort data = grund.entreprenourFAM&f_or_i&I;
	by id;
run;

proc sort data = grund.lindaFAMILY&f_or_i&I;
	by nybidnr;
run;

data grund.entreprenourFAMhist&f_or_i&I;
	merge grund.entreprenourFAM&f_or_i&I grund.entreprenour&f_or_i&I (rename = (nybidnr = id));
		by id;
	rename enterpr_inc_hh = enterpr_inc_hh&I closely_held_inc_hh = closely_held_inc_hh&I farmer_hh = farmer_hh&I 
			selfemployed_hh = selfemployed_hh&I closely_held_firm_hh = closely_held_firm_hh&I enterpr_inc_mod_hh = enterpr_inc_mod_hh&I
			enterpr_inc = enterpr_inc&I closely_held_inc = closely_held_inc&I inc_type = inc_type&I closely_held_firm = closely_held_firm&I;
	keep id enterpr_inc_hh closely_held_inc_hh farmer_hh selfemployed_hh closely_held_firm_hh enterpr_inc closely_held_inc inc_type closely_held_firm enterpr_inc_mod_hh;
run;

%end;
%mend;
%m0bbbaabc(f);
%m0bbbaabc(i);

data grund.entreprenourFAMhistf;
	merge grund.entreprenourFAMhistf1987-grund.entreprenourFAMhistf2007;
		by id;
run;

data grund.entreprenourFAMhisti;
	merge grund.entreprenourFAMhisti1987-grund.entreprenourFAMhisti2007;
		by id;
run;


**************************************************;
**Bankruptcies per municipality					**;
**************************************************;

%macro m0aab(konkurs);
data &konkurs;
set original.&konkurs;
C5 = lag(C1);
C6 = lag2(C1);
C7 = lag3(C1);
if C3 = 'EF' then C1 = C5;
if C3 = 'HB' then C1 = C6;
if C3 = 'PP' then C1 = C7;
drop C5-C7;
run;
%mend;
%m0aab(konkurs19941996);
%m0aab(konkurs19971999);
%m0aab(konkurs20002002);
%m0aab(konkurs20032005);
%m0aab(konkurs20062008);
%m0aab(konkurs19941996anst);
%m0aab(konkurs19971999anst);
%m0aab(konkurs20002002anst);
%m0aab(konkurs20032005anst);
%m0aab(konkurs20062008anst);


%macro m0aaa(typ,type);
data grund.bankruptcies&type;
	merge konkurs19941996 konkurs19971999 konkurs20002002 konkurs20032005 konkurs20062008;
		by C1 C4;
		where C3 = &typ;
	
	bankruptcies&type.1994 = sum(of v1994M01-v1994M12);
	bankruptcies&type.1995 = sum(of v1995M01-v1995M12);
	bankruptcies&type.1996 = sum(of v1996M01-v1996M12);
	bankruptcies&type.1997 = sum(of v1997M01-v1997M12);
	bankruptcies&type.1998 = sum(of v1998M01-v1998M12);
	bankruptcies&type.1999 = sum(of v1999M01-v1999M12);
	bankruptcies&type.2000 = sum(of v2000M01-v2000M12);
	bankruptcies&type.2001 = sum(of v2001M01-v2001M12);
	bankruptcies&type.2002 = sum(of v2002M01-v2002M12);
	bankruptcies&type.2003 = sum(of v2003M01-v2003M12);
	bankruptcies&type.2004 = sum(of v2004M01-v2004M12);
	bankruptcies&type.2005 = sum(of v2005M01-v2005M12);
	bankruptcies&type.2006 = sum(of v2006M01-v2006M12);
	bankruptcies&type.2007 = sum(of v2007M01-v2007M12);
	bankruptcies&type.2008 = sum(of v2008M01-v2008M12);
keep C1 bankruptcies&type.1994-bankruptcies&type.2008;
rename C1 = muni;
run;

%mend;
%m0aaa('HB',HB);
%m0aaa('AB',AB);
%m0aaa('PP',PP);
%m0aaa('EF',EF);


%macro m0abb(typ,type);
data grund.bankruptciesempl&type;
	merge konkurs19941996anst konkurs19971999anst konkurs20002002anst konkurs20032005anst konkurs20062008anst;
		by C1 C4;
		where C3 = &typ;
	
	bankruptciesempl&type.1994 = sum(of v1994M01-v1994M12);
	bankruptciesempl&type.1995 = sum(of v1995M01-v1995M12);
	bankruptciesempl&type.1996 = sum(of v1996M01-v1996M12);
	bankruptciesempl&type.1997 = sum(of v1997M01-v1997M12);
	bankruptciesempl&type.1998 = sum(of v1998M01-v1998M12);
	bankruptciesempl&type.1999 = sum(of v1999M01-v1999M12);
	bankruptciesempl&type.2000 = sum(of v2000M01-v2000M12);
	bankruptciesempl&type.2001 = sum(of v2001M01-v2001M12);
	bankruptciesempl&type.2002 = sum(of v2002M01-v2002M12);
	bankruptciesempl&type.2003 = sum(of v2003M01-v2003M12);
	bankruptciesempl&type.2004 = sum(of v2004M01-v2004M12);
	bankruptciesempl&type.2005 = sum(of v2005M01-v2005M12);
	bankruptciesempl&type.2006 = sum(of v2006M01-v2006M12);
	bankruptciesempl&type.2007 = sum(of v2007M01-v2007M12);
	bankruptciesempl&type.2008 = sum(of v2008M01-v2008M12);

keep C1 bankruptciesempl&type.1994-bankruptciesempl&type.2008;
rename C1 = muni;
run;

%mend;
%m0abb('HB',HB);
%m0abb('AB',AB);
%m0abb('EF',EF);


data grund.bankruptcies;
	merge grund.bankruptciesHB grund.bankruptciesAB grund.bankruptciesPP grund.bankruptciesEF grund.bankruptciesemplHB grund.bankruptciesemplAB grund.bankruptciesemplEF original.befolkningperkommun;
		by muni;
run;

%macro m0add;
%do I = 1994 %to 2007;

data grund.bankruptcies&I;
	set grund.bankruptcies;
		keep muni bankruptciesHB&I bankruptciesAB&I bankruptciesPP&I bankruptciesEF&I bankruptciesemplHB&I bankruptciesemplAB&I bankruptciesemplEF&I v&I;
		rename muni = muni&I v&I = pop_muni&I;
run;

%end;
%mend;
%m0add;


proc sort data = original.konkurser19821993 out = konkurser19821993temp (rename =(kommun = muni));
	by kommun;
run;

proc sort data = original.befolkningperkommun2 out = befolkningperkommun2;
	by muni;
run;

data konkurser19821993;
merge konkurser19821993temp befolkningperkommun2;
where muni>'0';
by muni;
run;


%macro m0aee;
%do I = 1982 %to 1993;

data grund.bankruptcies&I;
	set konkurser19821993;
		where year = &I;
		keep muni antalkonkurser antalanstallda v&I;
		rename muni = muni&I antalkonkurser = bankruptcies&I antalanstallda = bankruptciesempl&I v&I = pop_muni&I;
run;

%end;
%mend;
%m0aee;


**New, existing, and closed firms. Based on data from Bolagsverket;
data newfirms;
	set original.bolagsverket_stat_nystartade;
		type = 'new';
run;

data closedfirms;
	set original.bolagsverket_stat_avslutade;
		type = 'clo';
run;

data existingfirms;
	set original.bolagsverket_stat_samtliga;
		type = 'all';
run;

data allfirms;
	set newfirms closedfirms existingfirms;
run;

data allfirms2;
length muni4 $2 lan4 $2 muni $4;
set allfirms;
	lan2 = lan + 100;
	lan3 = put(lan2,3.);
	lan4 = substr(lan3,2,2);
	muni2 = kommun + 100;
	muni3 = put(muni2,3.);
	muni4 = substr(muni3,2,2);

	muni = lan4 || muni4; 
	drop lan2 lan3 lan4 muni2 muni3 muni4;
run;


%macro m0adde(typ,typ2);
%do I = 1984 %to 2007;

data allfirms&typ&I;
	set allfirms2;
	where ar = &I and type = &typ2;
	rename aktiebolag = ab_&typ&I handelsbolag = hb_&typ&I enskild_naringsidkare = en_&typ&I;
	keep muni aktiebolag handelsbolag enskild_naringsidkare;
run;

proc sort data = allfirms&typ&I;
	by muni;
run;

%end;
%mend;
%m0adde(new,'new');
%m0adde(all,'all');
%m0adde(clo,'clo');

%macro m0addf;
%do I = 1984 %to 2007;

data grund.firms&I;
	merge allfirmsnew&I allfirmsall&I allfirmsclo&I;
		by muni;
	rename muni = muni&I ab_clo&I = ab_closed&I hb_clo&I = hb_closed&I en_clo&I = en_closed&I;
	keep muni ab_all&I hb_all&I en_all&I ab_new&I hb_new&I en_new&I ab_clo&I hb_clo&I en_clo&I;
run;

%end;
%mend;
%m0addf;


******************************************************************************************************************;
******************************************** END OF CODE *********************************************************;
******************************************************************************************************************;

